Q

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

This was first published in November 2002
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close