date - year before 1753 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

date - year before 1753

Author

Topic

jstranger
Starting Member

11 Posts

Posted - 2010-05-10 : 09:36:52

I appear to have hit the 1753 issue as I have just had an insert error with SqlDateTime overflow error etc. where the input value is #1/1/1716# whereas another insert with #21/6/1753# worked OK. But I had thought that I had avoided this issue by using the data datatype rather than datetime. According to Books Online, the date datatype can handle a range of 0001-01-01 through 9999-12-31. The table column is a date and my SP has the relevant parameter as a date. So what else do I need to do? Or is Books Online plain wrong?

Jon

Jon Stranger

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-10 : 09:45:43

I think you get that error when you assign that value in a .NET date variable
Use string variable and assign the date in YYYYMMDD format to the table

Madhivanan

Failing to plan is Planning to fail

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-10 : 09:52:15

What's the exact error message? Do you get the error if you call the SP from SQL management studio?

--
Gail Shaw
SQL Server MVP

jstranger
Starting Member

11 Posts

Posted - 2010-05-10 : 10:08:24

The exception reported back to ASP.NET is 'SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.' I believe this is a SQL Server detected error.

I am passing the parameter with a .NET TypeCode of DateTime (since I have no alternative and that in any case accepts the value of 1/1/1716). My SP has that parameter defined as a date datatype. So I suspect that the SP first validates the parameter as a datetime (which is what the error message suggests) even though I have declared the parameter as date. But the more important question is how to achieve what I want. Surely Microsoft gave some thought as to how to use the new datatype - obviously I need to be able to pass such a value to my SP!

Jon Stranger

jstranger
Starting Member

11 Posts

Posted - 2010-05-10 : 10:15:36

Sorry - didn't answer the last part of your response. Yes, I can specify a value of '1/1/1716' in an update query in Management Studio. So it is just a matter of how I get it into the SP from ASP.NET.

Jon Stranger

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-10 : 10:23:38

What version of ASP.Net?

--
Gail Shaw
SQL Server MVP

jstranger
Starting Member

11 Posts

Posted - 2010-05-10 : 10:25:01

ASP.NET 3.5.

Jon Stranger

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-10 : 10:58:34

The SQL Server DATE and DATETIME2 datatype2 support times as old as 0001-01-01.

Example:

select
	SmallestDate = convert(date,'00010101 00:00:00.0000000'),
	LargestDate  = convert(date,'99991231 23:59:59.9999999'),
	SmallestDT2 = convert(datetime2,'00010101 00:00:00.0000000'),
	LargestDT2  = convert(datetime2,'99991231 23:59:59.9999999')

Results:

SmallestDate LargestDate SmallestDT2                 LargestDT2                  
------------ ----------- --------------------------- --------------------------- 
0001-01-01   9999-12-31  0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999

It's likely that your error is coming from your front end. SQL Server would return errors like these:

For DATETIME2:
Server: Msg 517, Level 16, State 3, Line 1
Adding a value to a 'datetime2' column caused an overflow.
OR
Server: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2010-05-10 : 11:14:45

" I am passing the parameter with a .NET TypeCode of DateTime (since I have no alternative ..."

I expect the ASP datatype you are using is conventional DATETIME and doesn't support "really old dates" thus provides an error arriving in SQL as DATETIME - before it gets a chance to Cast it to DATE

Hence Madhi's suggestion to use a String instead of a Datetime datatype (and to use YYYYMMDD format so that it is unambiguous to the SQL Parser which will cast it to DATE - we hope! otherwise accept it as a VARCHAR in your SQL code and explicitly cast it yourself in the SQL statement)

jstranger
Starting Member

11 Posts

Posted - 2010-05-10 : 11:16:45

Thanks for the reply. Yes, I understand that the date datatype will support old dates - that is why I picked it. And I agree that it is probably an ADO issue rather than SQL Server - I suspect the issue arises in the creation of the SqlParameter with TypeCode of DateTime - which I had assumed to be equivalent to the .NET DateTime object but is more likely to be equivalent to the old SQL datetime datatype (although it is odd that the code does not barf immediately when I create the parameter). But surely Microsoft could have guessed that someone might actually want to use the new datatype in an ASP.NET context?

Jon Stranger

jstranger
Starting Member

11 Posts

Posted - 2010-05-10 : 11:21:57

quote:
Originally posted by Kristen

" I am passing the parameter with a .NET TypeCode of DateTime (since I have no alternative ..."

I expect the ASP datatype you are using is conventional DATETIME and doesn't support "really old dates" thus provides an error arriving in SQL as DATETIME - before it gets a chance to Cast it to DATE

Hence Madhi's suggestion to use a String instead of a Datetime datatype (and to use YYYYMMDD format so that it is unambiguous to the SQL Parser which will cast it to DATE - we hope! otherwise accept it as a VARCHAR in your SQL code and explicitly cast it yourself in the SQL statement)

You are probably right - trouble is that my original dates are in a variety of formats and I have already written quite a bit of code in ASP.NET to get them into a uniform date format. To have to go back to square one and try to do the same in SQL is extremely disappointing. This was a rather half-baked 'enhancement' by Microsoft. How confident are you that SQL is up to the job?

Jon Stranger

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-10 : 11:25:45

ASP.NET 3.5 was released in Novemver 2007, before the release of SQL Server 2008, so it may not support that datatype.

ASP.NET 3.5 Service Pack 1 was released in August 2008 3.5 and ASP.NET 4.0 was release in April 2010. Maybe 3.5 SP1 or 4.0 has support for DATETIME2.

This page indicates that it is supported in .NET Framework 4
Date and Time Data in SQL Server 2008 (ADO.NET)
http://msdn.microsoft.com/en-us/library/bb675168.aspx

CODO ERGO SUM

jstranger
Starting Member

11 Posts

Posted - 2010-05-11 : 01:33:07

It turns out that the Parameter class also allows used of the DbType enumeration (which includes Date and DateTime2) in its constructor instead of TypeCode (which doesn't). I had always used TypeCode so hadn't spotted this. Don't know which version this happened in but I am using 3.5 SP1.

Jon Stranger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources