get current date - 30 days

[bosoxer1]

Need to create db2 where clause to het current day - 30 days, in the format of 2006051800 to compare against datetime field. 00 can be appended, I hope.

Thanks in advance.

 

[MarcLodge]

When you say you are comparing against a datetime field, I assume you don't mean a proper db2 datetimestamp column.

The following will give you a CHAR output and assumes the local db2 default date format to be CCYY-MM-DD:

SELECT SUBSTR(CHAR(CURRENT DATE),1,4)
||
SUBSTR(CHAR(CURRENT DATE),6,2)
||
SUBSTR(CHAR(CURRENT DATE),9,2)
||'00'
FROM SYSIBM.SYSDUMMY1

Hope this helps

Marc

 

[ddiamond]

The following should also work:

select
char(curdate()-30 days-'0000-01-01'+101)
FROM SYSIBM.SYSDUMMY1
;

Marc's solution is probably more readable, however.

 

[ddiamond]

correction:

select
char(curdate()-30 days-'0000-01-01'+101)||'00'
FROM SYSIBM.SYSDUMMY1
;

 

[MarcLodge]

Dan,
My solution forgot to subtract 30 days! Doh!

SELECT SUBSTR(CHAR(CURRENT DATE - 30 DAYS),1,4)
||
SUBSTR(CHAR(CURRENT DATE - 30 DAYS),6,2)
||
SUBSTR(CHAR(CURRENT DATE - 30 DAYS),9,2)
||'00'
FROM SYSIBM.SYSDUMMY1