Max Date Value

Here is the maximum value for a datetime datatype in SQL Server:

9999-12-31 23:59:59.997

And the minimum:

1753-01-01 00:00:00.000

The maximum precision for a datetime value is 3 milliseconds. This is why the ending milliseconds above are shown as 997 instead of 999.

Here’s the proof to get the max datetime in case you are interested. To get the minimum, simply add a negative sign to the increments below.

DECLARE @datetime DATETIME

SET @datetime = GETDATE()


WHILE 1 = 1
BEGIN
    BEGIN TRY
        -- increment by days
        SET @datetime = DATEADD(dd,1,@datetime)
    END TRY
    BEGIN CATCH
        -- cannot increment by days anymore
        WHILE 1 = 1
        BEGIN
            BEGIN TRY
                -- increment by minutes
                SET @datetime = DATEADD(mi,1,@datetime)
            END TRY
            BEGIN CATCH
                -- cannot increment by minutes anymore
                BEGIN TRY
                    -- increment by seconds
                    SET @datetime = DATEADD(s,1,@datetime)
                END TRY
                BEGIN CATCH
                    -- cannot increment by seconds anymore
                    BEGIN TRY
                        -- increment by milliseconds
                        SET @datetime = DATEADD(ms,3,@datetime)
                    END TRY
                    BEGIN CATCH
                        -- cannot increment by seconds anymore
                        SELECT @datetime
                        BREAK
                    END CATCH
                END CATCH
            END CATCH
           
        END
        BREAK;
    END CATCH
END

This took 7 seconds to run on my machine, fyi.

6 comments
Gaurav 16 Jul 2016 at 7:39 am

DECLARE @datetime DATETIME

SET @datetime = GETDATE()

WHILE 1 = 1
BEGIN
BEGIN TRY
— increment by year
SET @datetime = DATEADD(YY,1,@datetime)
END TRY
BEGIN CATCH
— cannot increment by years anymore
WHILE 1 = 1
BEGIN
BEGIN TRY
— increment by Month
SET @datetime = DATEADD(mm,1,@datetime)
END TRY
BEGIN CATCH
— cannot increment by Months anymore
WHILE 1 = 1
BEGIN
BEGIN TRY
— increment by days
SET @datetime = DATEADD(dd,1,@datetime)
END TRY
BEGIN CATCH
— cannot increment by days anymore
WHILE 1 = 1
BEGIN
BEGIN TRY
— increment by minutes
SET @datetime = DATEADD(mi,1,@datetime)
END TRY
BEGIN CATCH
— cannot increment by minutes anymore
BEGIN TRY
— increment by seconds
SET @datetime = DATEADD(s,1,@datetime)
END TRY
BEGIN CATCH
— cannot increment by seconds anymore
BEGIN TRY
— increment by milliseconds
SET @datetime = DATEADD(ms,3,@datetime)
END TRY
BEGIN CATCH
— cannot increment by seconds anymore
SELECT @datetime
BREAK
END CATCH
END CATCH
END CATCH

END
BREAK;
END CATCH
END
BREAK;
END CATCH
END
BREAK;
END CATCH
END

3J 29 Jan 2015 at 8:09 pm

If you start with years, it executes almost immediately.

Scott Gartner 25 Sep 2013 at 11:02 pm

Derek, Here is a somewhat faster method to do this. This takes 46ms on my machine to calculate both the minimum and maximum date:

DECLARE @theDate DATETIME = GETDATE();
DECLARE @newDate DATETIME;
DECLARE @increment int = -1;

DECLARE @start DATETIME = GETDATE();
WHILE @increment 0 and datediff(ms, @theDate, @newDate) < abs(@increment*3))
OR (@increment < 0 and datediff(ms, @newDate, @theDate) < abs(@increment*3)))
BEGIN
IF (@increment = -1) PRINT 'Minimum: ' + CAST(CAST(@theDate as Datetime2(7)) AS nVarChar(30));
IF (@increment = 1) PRINT 'Maximum: ' + CAST(CAST(@theDate as Datetime2(7)) AS nVarChar(30));
BREAK
END
END TRY
BEGIN CATCH
END CATCH
SET @theDate = @newDate;
END CATCH
END

SET @increment = CASE @increment
WHEN -1 THEN 1
WHEN 1 THEN 2
END;
END

DECLARE @end DATETIME = GETDATE();

PRINT cast(DATEDIFF(millisecond, @start, @end) as nVarChar(5)) + 'ms runnning time';

Aleksey 28 Mar 2012 at 12:25 am

Strange

SELECT CAST(’9999-12-31 23:59:59.997′ AS datetime)

generates the “Out-of-Range” message on my SQL Server (Russian collation)

However the following statement works perfectly !

SELECT CONVERT(DATETIME, ‘9999-12-31 23:59:59.997’,21)

Eduardo 29 Aug 2015 at 6:18 am

Hi. I was wondering how cold is Seoul driung this coming 1st week of April? My wife and I are having our vacation and this is our first time to Korea. We are clueless about the current weather. Searched few website telling different stories about the weather. Since are are staying there. Maybe u can advice us on the weather and what to wear? Thanks.

Kevin 27 Mar 2012 at 2:24 pm

Fairly interesting, and I DO like your clever use of try/catch.
One thing I found (of really, no significance…)

select CAST(‘9999-12-31 23:59:59.998’ AS datetime)

returns 9999-12-31 23:59:59.997 (BUAHAHA, I stole your millisecond -Love, SQL Server)

While:

select CAST(‘9999-12-31 23:59:59.999’ AS datetime)

generates the “Out-of-Range” message.

So, on the one hand (as you pointed out) we lose precision (fairly insignificant, depending on application). On the other, it seems you can’t REALLY squeeze in that last millisecond before the calendar flops to Y10K

Featured Articles

 Site Author

  • Thanks for visiting!