GetDate trouble in DTS

[tbellomo]

This query will work in the query analyzer, but not it the DTS package:

I receive this error --
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Desc: {IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0204 - GETDATE in *LIBL type *N not found.

I am trying to hit the AS400 and pull information down that pertains to the current date, (or date -1). I think I've got it all correct, but it keeps erring on GETDATE. If I replace GETDATE with a static date, then it tells me that my 'convert' and 'varchar' aren't recognized.

Any guidance would be greatly appreciated.

Thanks,
TIMO

 

[MDXer]

Is getdate() supported in the AS400? It appears the error you are recieving originates at the 400. what is the datatype in the 400?

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[tbellomo]

the datatype of the field that i want to use getdate() as criteria for is formatted YYYYMMDD... i also converted the getdate() to match up with that, but the query simply won't parse... i've been told that I need to use parameters, but I've never done that before.

thanks
TIMO

 

[JimTekki]

Hi,
I'm trying the exactly same thing and managed to subtract a number of days from the AS400 system date.

SELECT DATE ({ fn CHAR({ fn CURDATE() }, USA) }) - 30 day AS START_DATE
FROM TABLE

What I can't get done is to re-format this date to YYYYMMDD format for comparison.
Thanks,
JT

 

[leveyp]

Have a look at thread513-849401