DB2 date format yyyymmdd

[supernovae]

HI

I have a query on the date format .
I am retrieving data from a SAP DB2 based on selection date.
In the tables , the dates in the table are displayed as yyyymmdd.
When i use the statements
select....
....
where create date = '20020821'(hardcoded)
or
where create date = '2002'||'08'||char(day(current date))

It works ...but when i change the selection to
select...
... where create date = char(year(current date) || char(month(current date)) || char(day(current date)))
the query didnt work ...
Weird..yes..and i cldnt find out y...

Anybody can help me wif this?
Thanks for any replies

 

[leveyp]

I guess your problem lies in the char function.
When you convert month(currentdate) to char you will lose the leading zero for months 01 - 09. The same same will happen for the day(currentdate)

 

[supernovae]

Thanks for the reply .
I have since tried concatenating the '0' with the rest of the statements but still it doesnt work ...

where create date = char(year(current date) || '0' || char(month(current date)) || char(day(current date)))

did i miss out anything ?

 

[MarcLodge]

What's the format of create date field? Is it a CHAR, DEC or INTEGER field? (I'm assuming from your earlier post, that it's not a DATE field)

Marc

 

[leveyp]

Try this:

select
substr(char(year(current date)),1,4)||
case when month(current date) <= 9 then
'0'||substr(char(month(current date)),1,1)
else substr(char(month(current date)),1,2) end
||
case when day(current date) <= 9 then
'0'||substr(char(day(current date)),1,1)
else substr(char(day(current date)),1,2) end

from yourtable

 

[supernovae]

Thanks alot!