Converting Datetime2(0) to Datetime

Question

Post reply

Converting Datetime2(0) to Datetime

tmitchelar

Hall of Fame

Points: 3391
More actions
July 20, 2012 at 9:56 am

#264446

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.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I can't recreate the problem using the date you provided.

Can you post a script that actually fails for you?

Lowell SSC Guru Points: 323522 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dcdanoland SSChasing Mays Points: 647 More actions · Answer 3

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

tmitchelar Hall of Fame Points: 3391 More actions · Answer 4

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.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 5

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.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 6

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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

dcdanoland SSChasing Mays Points: 647 More actions · Answer 8

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.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 9

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:

Converting Datetime2(0) to Datetime

Cookies on SQLServerCentral