January 7, 2013 at 11:35 pm
I receive this error when inserting in the database. I know my dates are good.
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
any ideas please. i know just about all of you know me from bugging all. I really do thank you for your help, comments, and examples.
January 8, 2013 at 12:38 am
Can you post the dates?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 8, 2013 at 3:08 am
This is bacause u have taken field as DateTime, u should try DateTime2 insted.
January 8, 2013 at 3:12 am
DateTime Range : January 1, 1753, through December 31, 9999
DateTime2 Range : 0001-01-01 through 9999-12-31
January 1,1 AD through December 31, 9999 AD
January 8, 2013 at 8:51 am
Changing to datetime2 did not work. any other ideas?
thanks
January 8, 2013 at 8:56 am
What application are you using to do the inserts? What happens if you run the same insert statements from Management Studio?
John
January 8, 2013 at 8:56 am
That's always from either an out-of-range date or a malformed date.
Usually see it where American-style dates (MM-DD-YYYY) are being inserted into something expecting Euro-style dates (DD-MM-YYYY), or vice-versa. Tries to put "day 15" into "month", and errors out.
Test for that kind of thing in your data. That's the most common cause I've run into.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 9:07 am
I am using vb.net 2010 pro. I am new at this. Maybe i need to do a conversion or somthing in vb. Which I do not know how to do.
thanks
January 8, 2013 at 9:08 am
Definitely could be an issue with in-flight data conversions of one sort or another.
You'll probably need to get a more experienced .NET dev to review your code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 9:19 pm
U should keep format of 'YYYY-MM-DD' while insert from VS..
January 9, 2013 at 12:56 pm
I use .NET 4.0 and they did not update the date range limits in the libraries to match the new range in SQL Sever. I will have to wait until I get home, but will try to find a workaround I came up with and post it here for you.
This was a one week research project for me so if I can find my fix it could save you a ton of time.
January 9, 2013 at 2:47 pm
Thanks so very much. It would look like MS would fix this.
I set my dates fields to "date" in sql and my time to "time(4)
January 9, 2013 at 3:00 pm
Different dev teams I guess. Not very efficient.
January 9, 2013 at 8:13 pm
OK, just so everyone understands, this is not a SQL problem. The .NET Framework has a special data type for SQL databases in System.Data.SqlTypes.SqlDateTime. For some really silly reason, this data type's MinValue method has not been expanded to use the new minimum date. Probably because no one at Microsoft even thought about adding a SqlDateTime2 type, or any of the others like SqlDate and SqlTime.
What I had to do is find the code in my data access library that used SqlDateTime.MinValue to check dates before sending them to the database and changed it to use DateTime.MinValue. That will use the proper minimum date.
Do be sure and use DateTime2 in SQL or the database will be the next thing that will throw an error.
Hope this helps.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply