IBM Mainframe Forum

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

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.

Hello,

Need a bit more info. . .

BEG_DT

How is this column defined in the table?

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.

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.

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.

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

Please try the following thing :

BEG_DT > '2009-02-22'