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'

 

[ColinM]

Could you use an ODBC driver for the remote database and set it up as a linked server? That way you wouldn't have to export/import the data daily.

 

[rajeevnandanmishra]

Hi ,
I am using SQL 6.5
When i am using the query with

myDate = getdate() - 1
i get this error.
Msg 206, Level 16, State 2
Operand type clash: int is incompatible with datetime



I think if you change your query as
------------------
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 datediff(dd,movement_date,getdate())=1
and transaction_type = 'grn'
------------------

May this solve your problem.