Why does creating a date older than 1 Jan 1753 from a string in TSQL throw conversion error?

Question

In SQL Server 2019 I run the following code without error:

declare @d1 as datetime;
set @d1 = cast('1 Jan 1753 00:00am' as datetime);
select @d1 'date';

But when I run this code it fails:

declare @d1 as datetime;
set @d1 = cast('1 Jan 1752 00:00am' as datetime);
select @d1 'date';

Giving the error:

Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The only difference is the first one is 1 Jan 1753, and the second is 1 Jan 1752.

Thank you for your time.

https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server — psj01, Apr 14 '21 at 04:45
As of SQL Server **2008**, I'd recommend ditching `DATETIME` due to its various limitations (like not supporting dates before 1753) - use `DATE` if you need date only (not time portion), or `DATETIME2(n)` if you need the time, too — marc_s, Apr 14 '21 at 04:55
@marc_s, it appears you can't subtract with DATETIME2: Operand data type datetime2 is invalid for subtract operator. — Scotty, Apr 14 '21 at 05:35
You should use **proper** method - like `DATEADD(....)` - not just subtract an `INT` from a date - what does that really mean?? What are you subtracting? Seconds? Minutes? Hours? Days? Years? Thank **goodness** that's no longer supported !! — marc_s, Apr 14 '21 at 05:37
@marc_s, I was doing the following to get the hours between two dates: declare @v1 as DATETIME;` declare @v2 as DATETIME; set @v1 = cast('1 Jan 2021 00:00am' as DATETIME); set @v2 = cast('2 Jan 2021 5:01am' as DATETIME); select cast( @v2 - @v1 as float) * 24; ` I was doing this because DATEDIFF_BIG was thrown an error that the number was to big when the dates were to far apart. How can I do this with a DATETIME2? — Scotty, Apr 15 '21 at 00:31

score 1 · Accepted Answer · edited Apr 14 '21 at 05:39

1

According to Microsoft, the minimum date for a DATETIME column is 1/1/1753. Use DATE instead. It can go all the way back to 1/1/1.

edited Apr 14 '21 at 05:39

Dale K

answered Apr 14 '21 at 04:54

Jim

1 Answers1