SQL DateTimeOverflow

Question

SQL DateTimeOverflow

j.dibble

SSChasing Mays

Points: 618
More actions
March 18, 2010 at 8:23 am

#218019

Hi
I have a SQL database and tables are inserted overnight.
I have a record in this table (see attached) that is throwing the above error when I try and insert into SQL.
Can anyone advise how I can find the narrow down and find the problem record?
And how I could then go about ensuring the insert works .
Many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tim Weintz SSCommitted Points: 1622 More actions · Answer 1

One rather brute force method would be to write a cursor that inserts one record at a time and then capture the error/identify the record via try/catch.

LutzM SSC Guru Points: 107049 More actions · Answer 2

I'm guessing you 're trying to insert data in the wrong format.

Most probably it's caused by the format you're trying to insert conflicting with the current setting of DATEFORMAT.

Example:

SET DATEFORMAT DMY

--will work

INSERT INTO a SELECT '12-13-2010'

--won't work

INSERT INTO a SELECT'13-12-2010'

If this doesn't help to solve the issue please post the full error message together with some sample data in SQL format (=INSERT INTO ... SELECT...) instead of an attached Excel file.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Insert the data into a staging table with all VARCHAR() columns. Use ISDATE and couple of other checks on the date columns to validate the date BEFORE you try to insert it into the final table. Write all "bad" rows to an "errata" table for later investigation and repair.

As a sidebar, you should never import data to final tables... always import to a staging table and do your validations there.

As another sidebar, there are "switch settings" in both BCP and Bulk Insert (as of 2k8) that will allow you to basically "ignore" error rows and capture them in an "errata" file. If the staging table has a DateTime datatype and a bad date comes in, the bad-date-row (and possibly the next rows) will automatically be routed to an errata file. Of course, that will work fine for direct inserts into the final table, as well, but I'd get out of that habit. Like I used to tell my step-son... you don't know where that's been... don't put it in your mouth until you wash it. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

I think Jeff has what I'd suggest. Get it staged, then clean it up

Paul White SSC Guru Points: 150467 More actions · Answer 5

You could also use an SSIS package.

It has built-in features to do almost all of the stuff Jeff is suggesting, just by connecting a few dots.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi