INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

I need to subtract one day from current date in DB2

I need to subtract one day from current date in DB2

I need to subtract one day from current date in DB2

(OP)
Hello,

I have this query in DB2

SELECT * FROM DTINBS WHERE DTDCR = DAYS(CURRENT_DATE) - 1

The format for the where clause should be in YYYYMMDD for it work properly. If I change this query this

SELECT * FROM DTINBS WHERE DTDCR = 20120322 - 1

Everything works no problem, but I should be able to get today's date and subtract one day.

Any help will be appreciate it.

Thank you.

RE: I need to subtract one day from current date in DB2

CURRENT_DATE delivers result in DATE format, but there is possible to store date in numeric format with 8 digits /e.g. DECIMAL(8,0)/ as YYYYMMDD too (often used in COBOL programs).

If this is your case, then you have to do a conversion from DATE to number - For example something like this:

CODE

select                     
  CURRENT_DATE as TODAY_DATE,

  (CURRENT_DATE - 1 DAY) as YESTERDAY_DATE,

  YEAR(CURRENT_DATE)*10000 +
  MONTH(CURRENT_DATE)*100 +
  DAY(CURRENT_DATE) as TODAY_YYYYMMDD,

  YEAR(CURRENT_DATE - 1 DAY)*10000 +
  MONTH(CURRENT_DATE - 1 DAY)*100 +
  DAY(CURRENT_DATE - 1 DAY) as YESTERDAY_YYYYMMDD          
from sysibm.sysdummy1
;
Output:

CODE

TODAY_DATE  YESTERDAY_DATE  TODAY_YYYYMMDD   YESTERDAY_YYYYMMDD
2012-03-23    2012-03-22        20120323         20120322  
Now, when I have a table, where the record creation date DATCREATED is stored in numeric format YYYYMMDD, then I can do this selection, to get the records from yesterday:

CODE

select *
from MYLIB.MYTABLE
where
  DATCREATED =
  -- yesterday
  YEAR(CURRENT_DATE - 1 DAY)*10000 +
  MONTH(CURRENT_DATE - 1 DAY)*100 +
  DAY(CURRENT_DATE - 1 DAY)
;

RE: I need to subtract one day from current date in DB2

Jorame,
I think that your problem is that the column DTDCR is not a date but in fact an 8 digit number. You will need to convert that number to a date format before you can compare it to any date value. The best way to achieve this I think is as follows:

CODE

SELECT * FROM DTINBS
WHERE DATE(SUBSTR(CHAR(DTDCR),1,4)
           ||'-'||
           SUBSTR(CHAR(DTDCR),5,2)
           ||'-'||
           SUBSTR(CHAR(DTDCR),7,2)) = CURRENT_DATE - 1 DAY

This should give you what you are after.

Marc

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close