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

Disparate date-a types

I have a date in the string format of dd/mm/yyyy. I want to compare a column that is of date datatype with this string. I have no idea of the format in which the date is stored in the database. My database is DB2 v8.1.
Well, the best approach would probably be to convert your date in the string format to a DB2 date. Then you can do a comparison simply using SQL. This approach, or a variation thereof, would work for any DBMS, as long as you know the formats that are acceptable to the date/time functions used by the DBMS.

For DB2, you need to get your string into a valid string representation of a date so that you can use the DATE function to convert the data. Use YYYY-MM-DD to represent the date. So, we need to convert your DD/MM/YYYY into YYYY-MM-DD. This can be done with string manipulation functions.

First, use the SUBSTR function to break the character column apart into the separate components. For example, SUBSTR(column,1,2) returns the day component, SUBSTR(column,4,2) returns the month and SUBSTR(column,7,4) returns the year.

Then you can concatenate all of these together into a format that DB2 recognizes; for example, the desired string representation of a date in DB2. This can be done as follows:

SUBSTR(expression,7,4) || "-" || SUBSTR(expression,1,2) || "-" || SUBSTR(expression ,4,2)
Then you can use the DATE function to convert this character string into a DATE that DB2 will recognize. This is done as follows:
DATE( SUBSTR(expression,7,4) || "-" || SUBSTR(expression,1,2) || "-" || SUBSTR(expression ,4,2) )
The result of this can be used in date arithmetic with other dates (or date durations) or compared to DB2 date columns in SQL.

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.