GETDATE() method for DB2

Question

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...

What do you exactly want to achieve ? What is your question ? Do you want a day previous to current day ? — user2919277, Oct 30 '13 at 11:39
Here's a little article that might help you out: http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html — steoleary, Oct 30 '13 at 11:53
select dateadd(dd,-1,getdate()) This will give you the day previous to current day. If this answers you question ... — user2919277, Oct 30 '13 at 11:54
@user2919277 - I'd say it's pretty clear that's what the OP is looking for - make that an answer. — Clockwork-Muse, Oct 30 '13 at 11:58

score 34 · Accepted Answer · answered Oct 30 '13 at 14:12

34

This will give you yesterday's date:

SELECT CURRENT DATE - 1 DAY FROM sysibm.sysdummy1

answered Oct 30 '13 at 14:12

Benny Hill

6,191
4
39
59

score 4 · Answer 2 · answered Jan 13 '15 at 16:17

If you want to know a certain date range as an alternative you can try the TIMESTAMPDIFF scalar function, read syntax diagramSkip visual syntax diagram The parameter: 16 indicates it will evaluate per days

The next example determines a range of 70 days as of now.

WHERE (TIMESTAMPDIFF(16, CHAR(SYSDATE- CURRENT DATE)) )<70 AND (TIMESTAMPDIFF(16, CHAR(SYSDATE- CURRENT DATE)) ) > -1

you can take a look on this link for all the details about this method which is supported by DB2: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000861.html?cp=SSEPGG_9.7.0%2F2-10-3-2-155

The reference is for DB2 LUW. A DB2 for IBM i reference can be seen at http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzscatimedifstmp.htm — Buck Calabro, Jan 13 '15 at 17:01

score -6 · Answer 3 · answered Oct 30 '13 at 12:00

-6

select dateadd(dd,-1,getdate())

answered Oct 30 '13 at 12:00

user2919277

246
2
10

There is no DATEADD() in standard SQL. It's a Microsoft extension. MySQL also has Date_Add(). Similar for GETDATE(). – user2338816 Apr 07 '14 at 02:55

GETDATE() method for DB2

3 Answers3