Date formatting within a sql query

Bhuvaneswari K · New User Joined: 15 Sep 2008 Posts: 17 Location: Chennai

Is it possible to format date (from 'yyyy-mm-dd' to 'dd-mm-yyyy' ) in a sql query? If so plz explain me with a suitable example.

dbzTHEdinosauer · Posted: Mon Sep 22, 2008 5:33 pm

look at the iso options for scalar functions or ALTDATE function.

anandinmainframe · Active User Joined: 31 May 2007 Posts: 171 Location: India

Hi Bhuvaneswari,
This will fetch your Applied_dtstmp as MM/DD/YYYY
DATE(A.APPLIED_DTSTMP)

anandinmainframe · Active User Joined: 31 May 2007 Posts: 171 Location: India

Hi Bhuvaneswari,
SELECT SUBSTR(CHAR(your APPLIED_DTSTMP),9,2)||SUBSTR(CHAR(your APPLIED_DTSTMP),6,2)
||SUBSTR(CHAR(your APPLIED_DTSTMP),1,4) can give ddmmyyyy

Bhuvaneswari K · New User Joined: 15 Sep 2008 Posts: 17 Location: Chennai

Thank you Anand. Also the following syntax would be more simpler i guess.

char(date_field, {USA, JIS, EUR, ISO, DEF})

DEF - Date and time format associated with the territory code.
EUR - Use the IBM standard for Europe date and time format.
ISO - Use the date and time format of the International Standards Organization.
JIS - Use the date and time format of the Japanese Industrial Standard.
LOC - Use the date and time format in local form associated with the territory code of the database.
USA - Use the IBM standard for U.S. date and time format.

Bhuvaneswari K · New User Joined: 15 Sep 2008 Posts: 17 Location: Chennai

Dick,

I tried using ALTDATE( date-field, input-format, output-format) but this syntax dint work out for me.
Can you help me out in finding the exact syntax for ALTDATE function?

acevedo · Active User Joined: 11 May 2005 Posts: 344 Location: Spain

ALTDATE is a Sample user-defined function that may be not installed in your shop... ask your dba.

dbzTHEdinosauer · Posted: Tue Oct 07, 2008 5:16 pm

ALTDATE is a User Defined Function shipped with DB2.
Vsn7, can be found in DSN710.SDSNSAMP (or whatever).
I have personnally never used it.
The documentation defines how it is to be CALLed, etc.
I think it is to be used for CALLs from C modules.

Regardless of UDF or scalar/columnar Functions, pay close attention to your Datatypes.
That is normally what trips me up when I try to 'cast' something or manipulate data with a function.
an example is you can not SUBSTR(decimal_column) but you can:
SUBSTR(DIGITS(decimal_column)

what the hell does that have to do with dates???? - could not quickly come up with an example for dates.

but the philosophy is the same, make sure that your column value is cast to the appropriate Datatype for the function that you are using.