SQL declare datetime - 1001-01-01
Multi tool use
Clash Royale CLAN TAG#URR8PPPSQL declare datetime - 1001-01-01
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?
datetime
datetime
5 Answers
5
The minimum valid date for a DateTime data type is January 1, 1753.
DateTime
Try DATETIME2, using:
DATETIME2
DECLARE @d4 DATETIME2 = '1001-01-01'
The minimum valid date for DATETIME2 is 0001-01-01
DATETIME2
0001-01-01
datetime is not a structure, it's a data type. I think that you have it confused with the DateTime structure in the .NET framework. You don't need to cast a date literal to assign it to a datetime2 variable. Also, was it you who downvoted all other answers?– Guffa
Mar 20 '13 at 16:50
datetime
DateTime
datetime2
@Guffa Ah yeah, thanks for the clarity :). Nah it wasn't me, I'll upvote you at the daily reset to thank you for the edit clarifications :).
– mattytommo
Mar 20 '13 at 21:18
You can't do that. The earliest date that a datetime value can have is 1753-01-01.
datetime
Recent versions of Sql Server have the data type datetime2 that has a larger range:
datetime2
declare @d1 datetime2 = '1001-01-01';
There is nothing wrong with your answer and it doesn't warrant a downvote at all. Seems like a drive-by serial downvoter came by
– mattytommo
Mar 20 '13 at 21:19
The minimum SQL datetime is January 1,1753, so your date is indeed out-of-range.
SQLServer datetimes must be 1/1/1753 < X < 12/31/9999
http://msdn.microsoft.com/en-us/library/ms187819.aspx
you will not be able to set a DateTime to that year, so you may want to store it as a string and leave it to the upper layer langague to deal with.
1cm thick layer of argon be a significantly poorer conductor of heat than air?
Is it appropriate if my former advisor is editor of a journal I submit to?
Plausible reason why my time machine can only go back a certain amount of time?
How to find Erdős' treasure trove?
How Many Licks Does It Take To Get To The Center Of A Tootsie Pop?
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Voting to reopen. Although the specific question is too localized, the real question is: what's the range of the
datetimetype in SQL Server. The accepted answer answers that question and provides a solution.– John Saunders
Oct 1 '13 at 15:43