SQL declare datetime - 1001-01-01

Question

I am trying to declare a datetime variable with the value 1001-01-01 00:00:00.000

I have tried the following approaches with no luck

declare @d1 datetime = '1001-01-01';
declare @d2 datetime = 10010101;
declare @d3 datetime = '1001-01-01 00:00:00';
declare @d4 datetime = cast ('1001-01-01' as datetime)

I get the following errors

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
Msg 242, Level 16, State 3, Line 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Msg 242, Level 16, State 3, Line 4
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Is it possible to declare a datetime variable which can hold the value 1001-01-01 00:00:00.000?

Answer 1

The minimum valid date for a DateTime data type is January 1, 1753.

Try DATETIME2, using:

DECLARE @d4 DATETIME2 = '1001-01-01'

The minimum valid date for DATETIME2 is 0001-01-01