Search code examples
sqlsql-servert-sqlibm-midrangedb2-400

GETDATE() method for DB2

I have been trying for a while now to get a similar method to GETDATE() in DB2 for i. So far I have found the following:

current date
current timestamp
current time

Would it be possible for me to:

 select specific, columns
 from table
 where datefield = current date - 1 day

Is this the most efficient way or is there some way I perhaps haven't found yet?

EDIT:

I currently have this:

WHERE datefield = - days(date('2013-10-28'))

although this isn't helpful as I will need to edit it every day the query runs.

Have now come to this:

WHERE datefield = VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD') - 1

Except this will not work on the first day of the month as 1 - 1 = 0 and there is no day 0 in a month...

Solution

  • This will give you yesterday's date:

    SELECT CURRENT DATE - 1 DAY FROM sysibm.sysdummy1