finding max possible date in ms sql server 2005+

Question

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)

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.

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)

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

I'm creating a new answer to this question just to address a couple of minor issues with scottm's otherwise good accepted answer.

1. According to the docs, the maximium value of the time component of a datetime is actually "23:59:59.997", so using a value of "23:59:59.999" will actually round up to midnight the following day. Where the date part is already given as 31st Dec 9999 this would attempt to round up to 1st Jan 10000 which gives an out-of-range error.

https://msdn.microsoft.com/en-us/library/ms187819(v=sql.105).aspx says:

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

Time range: 00:00:00 through 23:59:59.997

2. The date literals used are in US format, and while these are not ambiguous for 31st Dec it's best to use ISO format. Nitpicky maybe, but I find reading dates in mm/dd/yyyy format very non-intuitive, and I suspect I'm not alone.

3. SQL needs the body of the function to be contained in a BEGIN/END block, so as written the example could not be created.

So my improved version of the max DateTime function is:

CREATE FUNCTION fnMaxDateTime()
RETURNS DateTime
AS
BEGIN
    RETURN CAST('9999-12-31 23:59:59.997' AS DateTime)
END

If you truly want an expiration date of "never", it might be better to store NULL rather than an arbitrary far-future date. While it is unlikely that the date will reach year 9999 without the code being "fixed", it is an illogical value to store for EndDate = never.