CODEWITHSUNDEEP
db2ibm-midrange
Toya
Toya

Reputation: 37

DB2 for IBM i (iSeries) Date - Need to Compare (Current Date - 1)

I am new to DB2 for IBM i (iSeries) syntax. I would like to compare a date field in a table to yesterday's date (current date - 1) for auditing purposes. However, I cannot get it to work. I receive a "[SQL0182] A date, time, or timestamp expression not valid" error. Any help is greatly appreciated. See code below. 

SELECT DECIMAL_FIELD
 FROM SCHEMA.TABLE
  WHERE DATE(INSERT(INSERT(DIGITS(DECIMAL_FIELD), 5, 0, '-'), 8, 0, '-')) > DATE(VARCHAR_FORMAT(TIMESTAMP_ISO(CURRENT DATE), 'YYYY-MM-DD')) - 1
AND DECIMAL_FIELD <> 0

Upvotes: 0

Views: 946

Answers (1)

James Allman
James Allman

Reputation: 41168

Specify the duration.

SELECT DECIMAL_FIELD
FROM SCHEMA.TABLE
WHERE DATE(INSERT(INSERT(DIGITS(DECIMAL_FIELD), 5, 0, '-'), 8, 0, '-')) >
    DATE(VARCHAR_FORMAT(TIMESTAMP_ISO(CURRENT DATE), 'YYYY-MM-DD')) - 1 DAY
    AND DECIMAL_FIELD <> 0

Also the comparison can be simplified:

SELECT DECIMAL_FIELD
FROM SCHEMA.TABLE
WHERE DATE(INSERT(INSERT(DIGITS(DECIMAL_FIELD), 5, 0, '-'), 8, 0, '-')) >
    CURRENT_DATE - 1 DAY
    AND DECIMAL_FIELD <> 0

Datetime arithmetic in SQL

Upvotes: 4

Related Questions