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.

[cc lang=”sql”]
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
[/cc]

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

Related Posts:
Post a comment

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

Reply to this comment
3J 29 Jan 2015 at 8:09 pm

If you start with years, it executes almost immediately.

Reply to this comment
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';

Reply to this comment
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)

Reply to this comment
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.

Reply to this comment
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

Reply to this comment

Featured Articles

 Site Author

  • Thanks for visiting!

Resources

css.php