Convertion of Date Format using SQL query in a shell script

dinesh1985 · November 19, 2009, 4:46am

When I write

Select date_field from TableA fetch first row only

I am getting the output as 09/25/2009.

I want to get the output in the below format

2009-09-25 i.e., MM-DD-YYYY. Please help

Scott · November 19, 2009, 4:50am

Hi.

Use to_char to get the format you want.

to_char( date_field, 'YYYY-MM-DD' )

dinesh1985 · November 19, 2009, 5:08am

I am getting the error as "SQL0440N No authorized routine named "TO_CHAR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884
"

Please help

aigles · November 19, 2009, 5:28am

The TO_CHAR function is perhaps specific to ORACLE.
You haven't specify wich database you are using.

Jean-Pierre.

dinesh1985 · November 19, 2009, 5:34am

Thanks for the response.I am using DB2 Database. Please help with the command for the same

aigles · November 19, 2009, 6:02am

Try :

Select CHAR(date_field, ISO)  from TableA fetch first row only

The date is displayed with the format YYYY-MM-DD

Jean-Pierre.

dinesh1985 · November 19, 2009, 6:10am

Hi aigles..
Thanks a lot!! it is working..May I know what is "ISO" ?

aigles · November 19, 2009, 6:16am

International Standards Organisation

Jean-Pierre.