Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Puzzled By DTS SQL Query Syntax

Author  Topic 

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-02-16 : 17:02:36
My DTS package is blowing up I suspect because a date field contains a date before 1900 (5-1-1899). It says "DBtype DBTimestamp - status6 - data overflow invalid character value for cast". I'm using a proprietary ODBC driver getting data from flat files.

I wanted to filter out the bad row, but am puzzled by the SQL syntax, where everything is surrounded by double-quotes. I tried to do a where > '1900-01-01' on the TABLE1-DOB date field, and it gave an error implying that I had to compare it to another column! Why would that make sense - shouldn't I be able to compare it to some sort of literal? It also gave syntax errors on my doing that. Then I tried to fit in a:

WHERE ISDATE([TABLE1-NUMBER]) = '1'

but it doesn't like the syntax of anything I put in. Here is the DTS SQL query that gets written automatically and that I want to modify to filter records out with TABLE1-DOB before 1900.

select "table1"."TABLE1-NUMBER", "table1"."TABLE1-DOB"
from "table1"
order by "table1"."TABLE1-NUMBER"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-17 : 07:23:57
Can try putting it into a datetime column instead of a smalldatetime
Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-02-17 : 12:31:21
Thanks russell, that worked - it also pointed me in the right direction where I discovered how to alter the data type during transformation to get rid of the other errors I had been encountering.


quote:
Originally posted by russell

Can try putting it into a datetime column instead of a smalldatetime

Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-17 : 15:10:23
Your welcome. Glad it helped you along.
Go to Top of Page
   

- Advertisement -