Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Changing the Date Format

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

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Fri Mar 27, 2009 4:41 pm    Post subject: Changing the Date Format
Reply with quote

Hi all,

I am having the TS value = 1993-06-07-14.52.43.580583

when i use the fuction DATE(UPDT_TS) the value is coming as 1993-06-07 (YYYY-MM-DD)

Is there any other fuction which can get me the date in MM/DD/YYYY Format.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Mar 27, 2009 4:52 pm    Post subject:
Reply with quote

Take a look at the ISO functions.
Any reason you can not manipulate date in your program.
Back to top
View user's profile Send private message
senthilnathanj

New User


Joined: 31 Jul 2007
Posts: 47
Location: chennai

PostPosted: Fri Mar 27, 2009 5:03 pm    Post subject:
Reply with quote

hi niki,
u can write the separate function for date conversion.
Code:
DATE_TO_DATE10: PROC(WS_DATE) RETURNS(CHAR(10));       
                                                         
  DCL WS_DATE          CHAR(26);                         
                                                         
  WS_DATE10 = (10)' ';                                   
                                                         
  IF WS_DATE ¬= '' THEN                                 
    DO;                                                   
      WS_DATE10 = SUBSTR(WS_DATE,5,2) || '/' ||         
                  SUBSTR(WS_DATE,7,2) || '/' ||         
                  SUBSTR(WS_DATE,1,4);                   
   END;                                                 
  ELSE                                                   
    WS_DATE10 = WS_DATE;                                 
  RETURN(WS_DATE10);                                     
END;   


just call the functiion
DATE_TO_DATE10(xxxx);
Back to top
View user's profile Send private message
murugan_mf

Active User


Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India

PostPosted: Fri Mar 27, 2009 7:11 pm    Post subject:
Reply with quote

Code:
SELECT                                         
SUBSTR(CHAR(DATE(UPDT_TS)),6,2)|| '/' ||         
SUBSTR(CHAR(DATE(UPDT_TS)),9,2)|| '/' ||         
SUBSTR(CHAR(DATE(UPDT_TS)),1,4) FROM TBL1; 
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Fri Mar 27, 2009 10:49 pm    Post subject: Reply to: Changing the Date Format
Reply with quote

Hi Niki

Try this

SELECT CHAR(SUBSTR(UPDT_TS,1,10), USA) FROM TABLE1;
The SUBSTR function is used because the argument is not a pure date datatype. If the argument is date datatype then you can avoid the use of the function SUBSTR.
DB2 is providing a number of functions to satisfy this type of requirements. Try to refer the DB2 manuals.
Please post the replay if you are able to get the desired result.

Regards
Raghu
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Mon Mar 30, 2009 11:16 pm    Post subject: Reply to: Changing the Date Format
Reply with quote

Hi Nikki

There is a correction in my previous post.
The correct statement is SELECT SUBSTR(CHAR(UPDT_TS,USA),1,10) FROM TABLE1

Regards
Raghu
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Mar 30, 2009 11:41 pm    Post subject:
Reply with quote

Why not just
Code:
SELECT CHAR(DATE(UPDT_TS),USA) FROM TABLE1
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Wed Apr 01, 2009 12:15 am    Post subject: Reply to: Changing the Date Format
Reply with quote

Hi Craq

That is absolutely the shortest sql fro the requirement.

Thanks
Raghu
Back to top
View user's profile Send private message
amitgoel29

New User


Joined: 05 Nov 2008
Posts: 3
Location: india

PostPosted: Sat Apr 04, 2009 12:05 pm    Post subject: Reply to: Changing the Date Format
Reply with quote

You can refer the below link to get more information in formatting date and time

http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html
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 Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us