Max Date Value
-
Posted on October 23, 2010 by Derek Dieter
-
3
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.
- Comments (RSS)
- Trackback
- Permalink
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
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';
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)
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.
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