Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Date formatting within a sql query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Bhuvaneswari K

New User


Joined: 15 Sep 2008
Posts: 17
Location: Chennai

PostPosted: Mon Sep 22, 2008 5:09 pm    Post subject: Date formatting within a sql query
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Sep 22, 2008 5:33 pm    Post subject:
Reply with quote

look at the iso options for scalar functions or ALTDATE function.
Back to top
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Tue Oct 07, 2008 9:40 am    Post subject: Reply to: Date formatting within a sql query
Reply with quote

Hi Bhuvaneswari,
This will fetch your Applied_dtstmp as MM/DD/YYYY
DATE(A.APPLIED_DTSTMP)
Back to top
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Tue Oct 07, 2008 10:26 am    Post subject: Reply to: Date formatting within a sql query
Reply with quote

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
Back to top
View user's profile Send private message
Bhuvaneswari K

New User


Joined: 15 Sep 2008
Posts: 17
Location: Chennai

PostPosted: Tue Oct 07, 2008 4:54 pm    Post subject: Re: Reply to: Date formatting within a sql query
Reply with quote

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.
Back to top
View user's profile Send private message
Bhuvaneswari K

New User


Joined: 15 Sep 2008
Posts: 17
Location: Chennai

PostPosted: Tue Oct 07, 2008 4:56 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Tue Oct 07, 2008 5:11 pm    Post subject: Reply to: Date formatting within a sql query
Reply with quote

ALTDATE is a Sample user-defined function that may be not installed in your shop... ask your dba.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 07, 2008 5:16 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us