Range of datetime data type

Following is the range of datetime data type:

Lower range(Minimum value) :
‘1752-01-01 00:00:00.000’ January 1st 1752 12 AM

Upper range(Maximum value) :
‘9999-12-31 23:59:59.998’ December 31st 9999 12 PM (milli seconds before next day)

Queries to check the same:
This query executes successfully:
[sql]
SELECT CAST( ‘9999-12-31 23:59:59.998’ AS DATETIME)
[/sql]

This query fails:
[sql]
SELECT CAST( ‘9999-12-31 23:59:59.999’ AS DATETIME)
[/sql]

This query executes successfully:
[sql]
SELECT CAST( ‘1753-01-01 00:00:00.000’ AS DATETIME)
[/sql]

This query fails:
[sql]
SELECT CAST( ‘1752-12-31 23:59:59.999’ AS DATETIME)
[/sql]

Applicable to: SQL Server 2005, SQL Server 2008,SQL Server 2008 r2,SQL Server 2012

Leave a Reply

Your email address will not be published. Required fields are marked *