I need to subtract one day from current date in DB2

[jorame]

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.

 

[mikrom]

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:

[COLOR=#804040][b]select[/b][/color]                     
  CURRENT_DATE [COLOR=#6a5acd]as[/color] TODAY_DATE,

  (CURRENT_DATE - [COLOR=#ff00ff]1[/color] DAY) [COLOR=#6a5acd]as[/color] YESTERDAY_DATE,

  YEAR(CURRENT_DATE)*[COLOR=#ff00ff]10000[/color] + 
  MONTH(CURRENT_DATE)*[COLOR=#ff00ff]100[/color] + 
  DAY(CURRENT_DATE) [COLOR=#6a5acd]as[/color] TODAY_YYYYMMDD,

  YEAR(CURRENT_DATE - [COLOR=#ff00ff]1[/color] DAY)*[COLOR=#ff00ff]10000[/color] + 
  MONTH(CURRENT_DATE - [COLOR=#ff00ff]1[/color] DAY)*[COLOR=#ff00ff]100[/color] + 
  DAY(CURRENT_DATE - [COLOR=#ff00ff]1[/color] DAY) [COLOR=#6a5acd]as[/color] YESTERDAY_YYYYMMDD          
[COLOR=#6a5acd]from[/color] sysibm.sysdummy1
;

Output:

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:

[COLOR=#804040][b]select[/b][/color] * 
[COLOR=#6a5acd]from[/color] MYLIB.MYTABLE
[COLOR=#6a5acd]where[/color] 
  DATCREATED = 
  [COLOR=#0000ff]-- yesterday[/color]
  YEAR(CURRENT_DATE - [COLOR=#ff00ff]1[/color] DAY)*[COLOR=#ff00ff]10000[/color] + 
  MONTH(CURRENT_DATE - [COLOR=#ff00ff]1[/color] DAY)*[COLOR=#ff00ff]100[/color] + 
  DAY(CURRENT_DATE - [COLOR=#ff00ff]1[/color] DAY)
;

 

[MarcLodge]

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:

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