Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SqlDateTime overflow Expand / Collapse
Author
Message
Posted Monday, January 7, 2013 11:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
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.
Post #1403983
Posted Tuesday, January 8, 2013 12:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 13,641, Visits: 11,516
Can you post the dates?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1404002
Posted Tuesday, January 8, 2013 3:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:40 AM
Points: 1,608, Visits: 381
This is bacause u have taken field as DateTime, u should try DateTime2 insted.
Post #1404085
Posted Tuesday, January 8, 2013 3:12 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:40 AM
Points: 1,608, Visits: 381
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
Post #1404087
Posted Tuesday, January 8, 2013 8:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
Changing to datetime2 did not work. any other ideas?
thanks
Post #1404295
Posted Tuesday, January 8, 2013 8:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 5,490, Visits: 10,345
What application are you using to do the inserts? What happens if you run the same insert statements from Management Studio?

John
Post #1404298
Posted Tuesday, January 8, 2013 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1404300
Posted Tuesday, January 8, 2013 9:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 12, 2013 7:25 AM
Points: 39, Visits: 144
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
Post #1404307
Posted Tuesday, January 8, 2013 9:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1404309
Posted Tuesday, January 8, 2013 9:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:40 AM
Points: 1,608, Visits: 381
U should keep format of 'YYYY-MM-DD' while insert from VS..
Post #1404535
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse