[thelist] bad sql data

[Ken Schaefer]

Hi,

SQL Server doesn't accept invalid dates. So every date/time in the database
at the moment *is* a valid date. Dates in SQL Server are not stored in
"different formats". There are only two formats: DateTime and smallDateTime.
For a DateTime the date and time are stored as two 4 byte integers. One is
the number of days before/after 1/1/1900 and one is the number of
milliseconds (well, to the nearest 13/100 of a second) past midnight. For a
SmallDateTime SQL Server uses two two-byte integers. This occurs
*irrespective* of your Regional settings. As far as SQL Server is concerned,
a datetime is just a number.

So, when you say you are inserting this data into another database, you must
be converting it to some kind of string representation of a date, and then
inserting it into the other database. At this point, SQL Server needs to
parse the string representation and attempt to convert it to an actual
date/time that it can store internally. And this parsing of a string
representation is what's failing.

What you can do is parse the date/time you extract, convert it to ISO format:
yyyymmdd hh:mm:ss and then insert it into the new database. If you are doing
a straight INSERT INTO...SELECT then use CONVERT() with 112 as the style (112
is ISO-style).

Cheers
Ken

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Robert Vreeland
Sent: Monday, 28 November 2005 2:33 PM
To: thelist at lists.evolt.org
Subject: Re: [thelist] bad sql data

<snip>
Ken wrote
I'm not sure what you mean by "out of range" dates. If the dates were not
valid, then they would never have been accepted by SQL Server in the first
place.
</snip>

To clarify, 'out of range':
If you have a date field and the server has it's date format set to US
format, then 13/09/2005 would be invalid as there is no 13th month. Same
value on a server set to European format would go through no problem. As the
sql server is grabbing this expected format from the OS, the setting can
change. The problem comes in using that data. Specifically, we have a
routine that makes a copy of a database. It starts by creating an empty
database from a script then populates it from the selected database, and
since dome of those dates are in the wrong format, it blows up.

Robert