COMMAND SQL CURRENTDATE-1 SYNTAX

[kenyoj]

Hi

I have written a command in crystal querying AS/400 (DB2) tables. I need a function similiar to crystals 'currentdate' so I can program the report to pick up records created on the previous day.
I have tried currentdate-1, (today()-1),date(-1).
Any suggestions would be gratefully recieved.
Thanks

 

[Madawc]

currentdate-1 should work. Try displaying the data - maybe it's not what you thought it was.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[Madawc]

Try writing the expressions in a formula field and see if it displays the previous day.

DateAdd will subract days, you 'add' a negative number. But it should not be needed.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[kenyoj]

Sorry Madawc, I have tried this and it doesn't work. I am running the SQL through I-series to be used in a command in Crystal Reports XIr2. This is the error message:

Message: [SQL0182] A date, time, or timestamp expression not valid. Cause . . . . . : One of the following has occurred: -- An operand of addition is a date and the other is not a date duration. -- An operand of addition is a time and the other is not a time duration. -- An operand of addition is a timestamp and the other is not a duration. -- An operand of subtraction is a date and the other is not a date, character, or date duration. -- An operand of subtraction is a time and the other is not a time, character, or time duration. -- An operand of subtraction is a timestamp and the other is not a timestamp, character, or duration. Recovery . . . : Correct the arithmetic expression so that it contains a valid date, time, or timestamp expression. Try the request again.

Processing ended because the highlighted statement did not complete successfully

 

[kenyoj]

Hi Madawc

I have found the answer! 'current date - 1 day' does the trick!

Thanks for replying to me, much appreciated.