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.