SQL Date functions!!!!

[richardmorrison]

I'm still having problems with the date function, so I'm going to tell you a little more about what I'm doing. I'm interrogating a remote database(UNIX network) using DTS in SQL 7 server. Initially I imported the database locally in order to test and verify my SQL statements more efficiently. What is happening is the getdate function works locally but not in the DTS procedure. The query is not accepted with the getdate function. Bearing in mind that the database to be interrogated is massive I only want the previous days transactions. Any suggestions!

The statement below works locally, but not in the DTS procedure.

select movement_quantity AS 'QUANTITY', movement_date AS 'DATE', product AS 'PRODUCT', transaction_type AS 'TYPE', warehouse AS 'WAREHOUSE' from geoff.st_history
where warehouse = 'C1'
and movement_date = getdate() -1
and transaction_type = 'grn'

 

[JoeMiller]

Did you try Date()-1 instead of GetDate()-1? I've never heard of the GetDate function so it must be a custom function. Date() returns the date of the system the SQL is being run on which sounds like what you're after.

HTH Joe Miller
joe.miller@flotech.net