Page 1 of 1

Date format in DB2

PostPosted: Wed Apr 01, 2009 10:58 pm
by nadeem86
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

Re: Date format in DB2

PostPosted: Thu Apr 02, 2009 3:19 am
by dick scherrer
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.

Re: Date format in DB2

PostPosted: Thu Apr 02, 2009 6:02 am
by dick scherrer
Hello,

Need a bit more info. . .

BEG_DT
How is this column defined in the table?

Re: Date format in DB2

PostPosted: Thu Apr 02, 2009 9:36 pm
by nadeem86
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.

Re: Date format in DB2

PostPosted: Fri Apr 03, 2009 12:26 am
by Bill Dennis
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.

Re: Date format in DB2

PostPosted: Fri Apr 03, 2009 8:28 am
by dick scherrer
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.

Re: Date format in DB2

PostPosted: Tue Aug 11, 2009 12:20 pm
by manishas
Nadeem,
I have tried to execute the query for comparing date and it worked.

Please try the following thing :

BEG_DT > '2009-02-22'