Date format in DB2



IBM's flagship relational database management system

Date format in DB2

Postby nadeem86 » Wed Apr 01, 2009 10:58 pm

I am a little confused with the date format in DB2 and need clarification.

1) While checking for a particular date in the WHERE clause in which format should i represent it? For eg. i want to check if begin date is GE 23/02/2009 then how do i code?

BEG_DT >= '23/02/2009' ---> Is this form of checking correct?

2) What is the internal format in which a date is stored in DB2 table?

3) I have learnt that DB2 format date is dd/mm/yyyy but when it stores internally, it gets stored in ISO format which is YYYY-MM-DD. IS my understanding correct? What is the difference between DB2 format and ISO format of date when it comes to storing and retrieving from database? Which one do we use?

Thanks in Advance
Nadeem
nadeem86
 
Posts: 2
Joined: Wed Apr 01, 2009 10:43 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Date format in DB2

Postby dick scherrer » Thu Apr 02, 2009 3:19 am

Hello Nadeem and welcome to the forum,

BEG_DT >= '23/02/2009' ---> Is this form of checking correct?
No, as the literal is a character field and the compare would be left to right which would not compare as you would want it.

Time permitting, i'll try to log back in later and see if i can find some example to post.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Date format in DB2

Postby dick scherrer » Thu Apr 02, 2009 6:02 am

Hello,

Need a bit more info. . .

BEG_DT
How is this column defined in the table?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Date format in DB2

Postby nadeem86 » Thu Apr 02, 2009 9:36 pm

BEG_DT is defined with datatype 'DATE'

Thank You for the response Dick. Also look into part 2 & 3 of my query and give suggestions. I had digged alot on this but was not able to come to a conclusion! Need help.
nadeem86
 
Posts: 2
Joined: Wed Apr 01, 2009 10:43 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Date format in DB2

Postby Bill Dennis » Fri Apr 03, 2009 12:26 am

Perhaps you could load the date literal into another "work" table and access that field for a comparison. Then the two compare on internal formats.

Disclaimer: I'm barely DB2 literate so this may be way off base.
Regards,

Bill Dennis

Disclaimer: My comments on this forum are my own and do not represent the opinions or suggestions of any other person or business entity.
Bill Dennis
 
Posts: 278
Joined: Thu May 15, 2008 9:45 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Date format in DB2

Postby dick scherrer » Fri Apr 03, 2009 8:28 am

Hello,

See if this helps clarify how dates are handled by db2:
http://publibz.boulder.ibm.com/cgi-bin/ ... qj10/2.9.7?

There is also the DATE function:
http://publibz.boulder.ibm.com/cgi-bin/ ... j10/3.2.17?

Something like SELECT . . . FROM . . WHERE BEG_DT >= DATE('2009-02-23') . . . may do what you want. This is not tested as i'm not connected to a mainframe tonight.

You should be able to do as Bill suggests also.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Date format in DB2

Postby manishas » Tue Aug 11, 2009 12:20 pm

Nadeem,
I have tried to execute the query for comparing date and it worked.

Please try the following thing :

BEG_DT > '2009-02-22'
manishas
 
Posts: 1
Joined: Mon Aug 10, 2009 3:23 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post