I know that the SQL Server date datatype is defined to have a range of 0001-01-01 to 9999-12-31. Is there any way to obtain those values using T-SQL without the use of magic numbers (e.g. without something like declare @MaxDate date = '12/31/9999')?
3 Answers
You definitely can't get the maximum, but I've also just learned that you can't get the minimum either.
You could do this with the datetime type:
SELECT CAST(0 AS datetime)
--returns 1900-01-01 00:00:00.000
But you can't do it with the date type:
SELECT CAST(0 AS date)
--returns Msg 529, Level 16, State 2, Line 1
--Explicit conversion from data type int to date is not allowed.
1 Comment
Conduit
Too bad. Odd choice, all things considered - the intent is pretty obvious. Seems maximum is out of reach as well...
So this doesn't work without Magic numbers, but if you need functions (I used hex notation as it's a bit more compact and easier to read for numbers this large)
SELECT CAST(CAST(CAST(0x2D247f AS BIGINT) AS DATETIME) AS DATE) --, CAST(0x2D247f AS BIGINT)
SELECT CAST(CAST(CAST(0xD1BA AS BIGINT) * -1 AS DATETIME) AS DATE) --, CAST(0xD1BA AS BIGINT) * -1
Although, on the lower end, SQL Server will allow 1/1/1:
SELECT CAST('0001-01-01' AS DATE)
But not negative dates (e.g. BC).
Comments
There is a sort of round about way of getting. But you could do this:
DECLARE @dateHighest nvarchar(400), @dateLowest nvarchar(400)
SET @dateHighest = 'powershell.exe ((([System.Data.SqlTypes.SqlDateTime]::MaxValue).Value).ToString(\""MM/dd/yyyy\""))'
SET @dateLowest = 'powershell.exe ((([System.Data.SqlTypes.SqlDateTime]::MinValue).Value).ToString(\""MM/dd/yyyy\""))'
DECLARE @useTopDate table
(
highestDate date
)
DECLARE @useBottomDate table
(
lowestDate date
)
INSERT INTO @useBottomDate EXEC xp_cmdshell @dateLowest
INSERT INTO @useTopDate EXEC xp_cmdshell @dateHighest
SELECT TOP 1 * From @useTopDate, @useBottomDate
If you need it in a Stored Proc or function you can use it later.
4 Comments
Dan Field
This would get the CLR min/max, not necessarily what SQL server is configured for.
Nitsch_D
@DanField, yep as per the note at the end, but in most cases they should be the same, just not sure of the use case for this by the asker.
Dan Field
They're not the same for MinValue on a
Datetime (but they are for a date). It might work better if you chagned your PS to 'powershell.exe (([System.Data.SqlTypes.SqlDateTime]::MaxValue... and MinValue. See stackoverflow.com/questions/3310569/…Nitsch_D
Editing now as it's a more elegant solution and good to know ;)
coerce those values?DateTime.MinValue) quite useful, was writing a query that might benefit, and realized I had no idea if they were available. Searching Google yielded no useful results.