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

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

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

Post dtsx packages to forum

First off, can you post .dtsx packages to the forum? I always end up cutting and pasting code, which is by is nature incomplete, as it generally has external dependencies (connections, variables).

Perhaps you cannot do so for security reasons?

Second, is there a good blog site that allows you to post .dtsx packages?

I'd rather post .dtsx links than cut and paste code (which half the time formats the code un-recognizably), or even worse, post package xml, which contains the layout information and possibly IL that makes it all but unreadable.
No, you can't post attachments here. There are a number of free hosting providers out there that will host your package for you. Also look at blogger.com to see if that works for you.

If it's just a one time deal, some of us around here can host it for you, if you want.|||Thanks Phil.

Posting sample packags (at least for myself) is not a one time deal ,

I'll look at blogger.com. Many of the blog sites I looked at only allow you to upload image/audio files.
|||

jaegd wrote:

Thanks Phil.

Posting sample packags (at least for myself) is not a one time deal ,

I'll look at blogger.com. Many of the blog sites I looked at only allow you to upload image/audio files.

Yep, including the one I run over at ssistalk.com, except there I run my own server, so it's easy for me to get non-image/audio files posted...