Datetime

Question

Datetime

Soumya Easwaran

SSC Veteran

Points: 259
More actions
June 30, 2004 at 2:16 am

#161574

Hi,
When I try to execute the following SQL:
select CONVERT( DATETIME,'99991231 23:59:59:999')
I get the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I understand this is because of the rounding that is happening,but is there any way by which I can obtain the same result.

Regards
Soumya

Viewing 6 posts - 1 through 5 (of 5 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

This is because the largest daterange date SQL can handle is 12/31/9999. But when you add the time in it can only reach :998 which will float to :997, when you used :999 the fact that datetime has "an accuracy of one three-hundredth of a second " means the 999 will float to :000 which throws it to the next hour and because is midnight the next date which pushes it out the legal range for SQL to handle.

Soumya Easwaran SSC Veteran Points: 259 More actions · Answer 2

Hi,

Thanks for the info.

That means in no way I can get the same result.The millisecond will have to be changed to 998 to avoid the error...right

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

What is your goal?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 4

That is correct. But like Frank asked, what is your goal?

vadba SSChampion Points: 11133 More actions · Answer 5

From BOL ( index=datetime data type, overview)

datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Thus, select CONVERT( DATETIME,'99991231 23:59:59:999') rounds up to 1/1/10000, whereas select CONVERT( DATETIME,'99991231 23:59:59:998') rounds down to 12/31/9999 23:59:997, which is the largest datetime value possible.