1

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')?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Conduit
  • 2,675
  • 1
  • 26
  • 39
  • 2
    what do you mean with `coerce those values`? – Lamak Feb 11 '16 at 19:09
  • Obtain, sorry! Poor choice of words - editing momentarily :) – Conduit Feb 11 '16 at 19:10
  • It still doesn't clarify what you want to do. When do you need to get those values? – Lamak Feb 11 '16 at 19:11
  • 1
    Question is why do you need that? – Mihai Feb 11 '16 at 19:11
  • The question is as stated - if I wanted a different methodology I would have asked for that instead. My hope is that this could be made type-ambiguous (e.g. `maxof(int)`, `maxof(decimal(3,2))`) – Conduit Feb 11 '16 at 19:12
  • Alright but still I`m curious.Any reason for the max range of a type?Didnt downvote. – Mihai Feb 11 '16 at 19:14
  • 1
    Here are some questions that might give you some insight: http://stackoverflow.com/questions/3825893/sql-server-function-to-return-minimum-date-january-1-1753, http://stackoverflow.com/questions/15157328/datetime-minvalue-and-sqldatetime-overflow – Bob Kaufman Feb 11 '16 at 19:16
  • 1
    Possible duplicate of [finding max possible date in ms sql server 2005+](http://stackoverflow.com/questions/548353/finding-max-possible-date-in-ms-sql-server-2005) – Brandon Feb 11 '16 at 19:17
  • @BobKaufman - yes, exactly! – Conduit Feb 11 '16 at 19:17
  • 1
    @Mihai My purpose is primarily academic... I find the C# analogs (e.g. `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. – Conduit Feb 11 '16 at 19:21

3 Answers3

1

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.
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Too bad. Odd choice, all things considered - the intent is pretty obvious. Seems maximum is [out of reach as well](http://stackoverflow.com/questions/548353/finding-max-possible-date-in-ms-sql-server-2005)... – Conduit Feb 11 '16 at 19:24
0

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.

Nitsch_D
  • 11
  • 1
  • 1
  • 4
  • 1
    This would get the CLR min/max, not necessarily what SQL server is configured for. – Dan Field Feb 11 '16 at 20:13
  • @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. – Nitsch_D Feb 11 '16 at 20:37
  • 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 http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server – Dan Field Feb 11 '16 at 21:33
  • Editing now as it's a more elegant solution and good to know ;) – Nitsch_D Feb 12 '16 at 18:56
0

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

Dan Field
  • 20,885
  • 5
  • 55
  • 71