I'm trying to pull data from our Time Clock database (ASA) and convert it to a SQL Server 2000 through DTS. The PunchDate field in the ASA db is an integer. The format for 1/1/2004 would be 20040101 in the ASA DB. I was able to create a transformation in DTS to convert this value to a date format, but I need to build the DTS query to always pull yesterdays data. I tried to convert the outcome of NOW() - 1 to this integer format and also tried to convert the integer into a true date so I can do this query based on yesterday for my automation extract. Everyway I try I get Syntax Errors, but no explination of where the error is coming from.
The DB and the ODBC drivers are for Adaptive Server 7.0