50

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

Improve this question

8 Answers 8

Reset to default
49

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I've always thought these were very odd choices. I'd much rather be able to use years before 1753 rather than years after, say, 2400.
I think its because of problems with which countries switch to Gregorian calendar for dates earlier than 1753
The accuracy is also strange, the documentation says it's "Rounded to increments of .000, .003, or .007 seconds"
As Tommy Heath mentions in his answer, attempting to cast '12/31/9999 23:59:59.9999' results in an out of range value error (at least in SQL 2008). According to the docs, the max time value of a datetime is "23:59:59.997" - so using ".999~" would try to round it up to the next day, in the year 10000!
While the general structure of this is correct there are issues. It will only work if the region of the server is set to US, and the precision of the datetime used is incorrect.
|
15

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)
Improve this answer

2 Comments

Upvoted as for use of correct date formats - ie ISO8601 UTC
Your answer, especially the convert one helped me :) Thank you
13

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.
Improve this answer

3 Comments

This looks like a observation not an answer
The documentation also states Accurancy is Rounded to increments of .000, .003, or .007 seconds
I just wasted a whole afternoon trying to find a bug when using C# DateTime.MaxValue due to SQL rounding up the last value. That's what you get when you don't consider the errors you get.
9

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

Improve this answer

Comments

8

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

  1. 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.

  2. 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
Improve this answer

Comments

2

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.

Improve this answer

3 Comments

It isn't terribly useful for writes but is useful for reads e.g. "WHERE val < ISNULL(@d, maxdate())" because it improves index utilization - weblogs.sqlteam.com/jeffs/archive/2007/09/18/…
this approach would nullify indexed searches on this field. the most performant way is always avoiding nullable columns if performance is important as this would allow queries to fully utilize indexes. Besides queries also become easier to construct where you don't have to remember to add (OR xxx IS NULL) clause to the end.
I would argue that for all or most practical use cases @KemalErdogan is correct in that having better/more consistent index utilization is better than optimizing for "semantics". While having an end_date of null semantically makes the most sense for something that never expires, the idea is easily conveyed/understood by a year of 9999. It also does simplify query conditions as well, which is another benefit of avoiding the NULL in this (and other) cases.
0

Not a viable solution in a production environment, but you could increment the date until you hit the out-of-bounds error and catch the result:

BEGIN TRY  
    DECLARE
        @Step_Cnt INT = 0
        , @Ms_Increment int = 0
        , @StartDate datetime = '9999-12-31T23:59:59.900'
        , @CurDate datetime
        , @MaxTries int = 10000;
        set @CurDate = @StartDate
    WHILE TRY_CONVERT( datetime, @CurDate ) is not null and @Step_Cnt < @MaxTries
    BEGIN
        /* handle variable accuracy "Rounded to increments of .000, .003, or .007 seconds" */
        set @Ms_Increment = 0
        WHILE dateadd( millisecond, @Ms_Increment, @CurDate ) = @CurDate 
        BEGIN
            SET @Ms_Increment = @Ms_Increment + 1;
            SET @CurDate = dateadd( millisecond, @Ms_Increment, @CurDate )
        END;
        set @Step_Cnt = @Step_Cnt + 1
    END;
    select 'OK' as [Status],@StartDate as [StartDate], @Step_Cnt as [NumSteps], @CurDate as [FinalDate]

END TRY  
BEGIN CATCH  
    select 'Caught' as [Status],@StartDate as [StartDate], @Step_Cnt as [NumSteps], @CurDate as [FinalDate]
END CATCH;
Improve this answer

Comments

-2

You can use

    Select CAST(2958463 as DATETIME)

This translates to 9999-12-31 00:00:00.000

Improve this answer

Comments