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: 6967
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: 6967
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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Formatting VB File Learncoholic DFSORT/ICETOOL 3 Mon Mar 20, 2017 12:29 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


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