Q
Problem solve Get help with specific problems with your technologies, process and projects.

DB2 date conversion

In my database date is stored in numeric format yymmdd i.e. for MAY 13, 2003 it is stored as 30513. I want to compare this date between two dates, one is the current date and the other is 1 year from current date. I can get the current date from sysibm.sysdummy1 but the format is 2003-05-13 which is not numeric. So my question is how can I convert the date to numeric format and compare my database date between the converted dates? Thanking you and waiting for your reply.

Well, you will need to use CURRENT DATE along with some DB2 functions and some additional logic. First of all, the following functions will return the day, month, and year component of a date:
DAY(CURRENT DATE)
MONTH(CURRENT DATE)
YEAR(CURRENT DATE)

So, for today, the first would return 12, the second 5, and the last 2003. To get that into your format you would have to do something like this:

YOUR_FORMAT_DATE = ((YEAR(CURRENT DATE) - 2000) * 10000) +
                    (MONTH(CURRENT DATE) * 100) +
                     DAY(CURRENT DATE)

That should return the date in the numeric format you want (at least until the year 2100). But you'd be much better off using DB2 dates in your tables!

This was last published in May 2003

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close