finding max possible date in ms sql server 2005+

In my SQL Server 2008 r2, I get these odd results (not that I'm ever going to miss those 3 milleseconds)

SELECT cast('12/31/9999 23:59:59.997' as datetime) --RETURNS '9999-12-31 23:59:59.997'
SELECT cast('12/31/9999 23:59:59.998' as datetime) --RETURNS '9999-12-31 23:59:59.997'
SELECT cast('12/31/9999 23:59:59.999' as datetime) --RETURNS The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The documentation says the range is January 1, 1753, through December 31, 9999.

I don't think there is a built in function, but you could create one that returns the maximum datetime value.

CREATE FUNCTION fn_max_date
RETURNS datetime
AS
return cast('12/31/9999 23:59:59.9999' as datetime)

Consult the documentation.

http://msdn.microsoft.com/en-us/library/ms187819.aspx

Date range
January 1, 1753, through December 31, 9999

There is no way to get the max datetime programatically.

If so it would be listed here:

http://msdn.microsoft.com/en-us/library/ms186724.aspx

CAST() seems to be dependent on the SQL Server language/culture.

On my German SQL Servers 2008 R2 and 2012 (@@language = 'Deutsch'), the following cast throws an error:

CAST('12/31/9999 23:59:59.997' AS DATETIME)

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Whereas this one works just fine:

CAST('31.12.9999 23:59:59.997' AS DATETIME)

SOLUTION

I think the safest approach is to specify the format with CONVERT():

/* ISO 8601 */
CONVERT(DATETIME, '9999-12-31T23:59:59.997', 126)