pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig HesselI've never seen anything like it. I'd start with DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga..
.
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
David Portas
SQL Server MVP
--sql