SqlDateTime overflow

  • 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.

  • 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

  • This is bacause u have taken field as DateTime, u should try DateTime2 insted.

  • 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

  • Changing to datetime2 did not work. any other ideas?

    thanks

  • What application are you using to do the inserts? What happens if you run the same insert statements from Management Studio?

    John

  • 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

  • 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

  • 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

  • U should keep format of 'YYYY-MM-DD' while insert from VS..

  • 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.

  • 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)

  • Different dev teams I guess. Not very efficient.

  • 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