finding max possible date in ms sql server 2005+

Question

is there a function like getdate() in ms 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

Answer 1

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

Answer 2

January 1, 1753, through December 31, 9999

Answer 3

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' as datetime)

Answer 4

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.