finding max possible date in ms sql server 2005+
Is there a function like GETDATE()
in Sql Server 2005 that let's you get the max possible date?
I do not want to find the highest date in a table. I want to get the max possible date that sql server will be able to store.
Basically, I want an expiration date of never
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.