July 20, 2012 at 9:56 am
Whenever I try to convert this - it says "Out of range value" has anyone had any luck converting a Datetime2(0) to a Datetime datatype?
My datetime2(0) value, for example, is '2012-01-01 00:00:00'
Thank you in advance.
July 20, 2012 at 10:09 am
I can't recreate the problem using the date you provided.
Can you post a script that actually fails for you?
July 20, 2012 at 10:45 am
just guessing here, converting zero to datetime is allowed; converting zero to datetime2 isnot.
select CONVERT(datetime,0)
select CONVERT(datetime2,0)
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to datetime2 is not allowed.
you could do a double convert, which works for me:
select CONVERT(datetime2, CONVERT(datetime,0))
Lowell
July 20, 2012 at 10:51 am
The following code converts datetime2(0) to datetime:
Declare@SomeDateTime2_0datetime2(0)
Declare@SomeDateTimedatetime
Set@SomeDateTime2_0='2012-01-01 00:00:00'
Set@SomeDateTime=Convert(date, @SomeDateTime2_0)
Select@SomeDateTime2_0As[@SomeDateTime2_0],
@SomeDateTimeAs[@SomeDateTime]
The result is the following resultset:
@SomeDateTime2_0@SomeDateTime
2012-01-01 00:00:002012-01-01 00:00:00.000
July 20, 2012 at 11:31 am
Actually - my error is:
Msg 242, Level 16, State 3, Line 1
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
July 20, 2012 at 11:33 am
dcdanoland (7/20/2012)
The following code converts datetime2(0) to datetime:
Declare@SomeDateTime2_0datetime2(0)
Declare@SomeDateTimedatetime
Set@SomeDateTime2_0='2012-01-01 00:00:00'
Set@SomeDateTime=Convert(date, @SomeDateTime2_0)
Select@SomeDateTime2_0As[@SomeDateTime2_0],
@SomeDateTimeAs[@SomeDateTime]
The result is the following resultset:
@SomeDateTime2_0@SomeDateTime
2012-01-01 00:00:002012-01-01 00:00:00.000
This works, but not when trying to select from a dynamic dataset.
July 20, 2012 at 11:39 am
Ok, I apologize in advance. It is giving me an out-of-range error on the following type of values:
0012-12-01 00:00:00
0015-01-01 00:00:00
0020-01-01 00:00:00
0010-12-01 00:00:00
0016-08-01 00:00:00
0016-01-01 00:00:00
0018-12-01 00:00:00
0015-12-01 00:00:00
July 20, 2012 at 11:44 am
tmitchelar (7/20/2012)
Ok, I apologize in advance. It is giving me an out-of-range error on the following type of values:0012-12-01 00:00:00
0015-01-01 00:00:00
0020-01-01 00:00:00
0010-12-01 00:00:00
0016-08-01 00:00:00
0016-01-01 00:00:00
0018-12-01 00:00:00
0015-12-01 00:00:00
Valid values for DATETIME:
January 1, 1753, through December 31, 9999
July 20, 2012 at 11:53 am
To demonstrate what Lynn posted, the following code shows the month, day and year parts of the dates you provided:
Declare @ConversionTestTable
(SomeDateTime2_0DateTime2(0) Not Null,
SomeDateTimeDateTimeNull
);
Insert Into @ConversionTest
(SomeDateTime2_0)
Values ('0012-12-01 00:00:00'),
('0015-01-01 00:00:00'),
('0020-01-01 00:00:00'),
('0010-12-01 00:00:00'),
('0016-08-01 00:00:00'),
('0016-01-01 00:00:00'),
('0018-12-01 00:00:00'),
('0015-12-01 00:00:00')
SelectSomeDateTime2_0,
DatePart(Month, SomeDateTime2_0) As [Month],
DatePart(Day, SomeDateTime2_0) As [Day],
DatePart(Year, SomeDateTime2_0) As [Year]
From@ConversionTest
The results are:
SomeDateTime2_0 MonthDayYear
0012-12-01 00:00:0012112
0015-01-01 00:00:001115
0020-01-01 00:00:001120
0010-12-01 00:00:0012110
0016-08-01 00:00:008116
0016-01-01 00:00:001116
0018-12-01 00:00:0012118
0015-12-01 00:00:0012115
For the first value, the Year part is 12 AD, not 1912 or 2012 AD. The earliest year that a DateTime can handle is 1753 AD. DateTime2 can handle 1 AD. This satisfied a long-ago request from the Roman Empire.
July 20, 2012 at 12:51 pm
Thanks a lot - I feel like a shmuck. I'm not too familiar with datetime2 and thought I was looking at 1912 data - not 12 AD! LOL - interested to hear how business wants to handle this :):hehe:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply