×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

DB2 date format yyyymmdd

DB2 date format yyyymmdd

DB2 date format yyyymmdd

(OP)
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




RE: DB2 date format yyyymmdd

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)

RE: DB2 date format yyyymmdd

(OP)
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 ?

RE: DB2 date format yyyymmdd

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

RE: DB2 date format yyyymmdd

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                          

RE: DB2 date format yyyymmdd

(OP)
Thanks  alot!

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! Already a Member? Login


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