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

Formatting NATURAL_DATE strings

We are generating a period dimension in a DB2 (7.2) warehouse using a bunch of auto-generated columns. Many of these columns require a formatted date string. But the DB2 date functions in question return integers, meaning that for a simple result of the form "CCYY-MM-DD", we're using the following:

CAST(YEAR(NATURAL_DATE) AS CHAR(4)) || '-' ||
RIGHT(DIGITS(MONTH(NATURAL_DATE)), 2) || '-' ||
RIGHT(DIGITS(DAY(NATURAL_DATE)), 2)
(1) Is there a shorthand way to do this (say, similar to C's "format" function)?

(2) Is there a way of forcing a result's data type without using CAST?

Looks like you've found the correct way to do this. I have to ask, what data type is the NATURAL_DATE column? If it is a DB2 DATE data type, then you should be able to use CHAR(NATURAL_DATE,ISO) instead. This will return the date as a character expression in the format you require (ISO).

For More Information

Dig Deeper on IBM DB2 management

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.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close