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
 

 

To convert date to DD/MM/YYYY format

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

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Oct 17, 2014 10:17 am    Post subject: To convert date to DD/MM/YYYY format
Reply with quote

Hi All,

While extracting the rows from DB2 table having Policy Eff date to a mainframe flat file, I would like to have the date format as DD/MM/YYYY.
Is it possible to do it through SQL query itself ?

I have tried the below query but it gives result in date format MM/DD/YYYY
Code:
SELECT ACCT_ID,
       CHAR(POL_EFF-DT,USA)
  FROM ABC table;


The expected result is

Code:
ACCT_ID         DATE
12345            20/12/2013
23456            12/07/2013   

Please help.

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

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Fri Oct 17, 2014 10:59 am    Post subject: Reply to: To convert date to DD/MM/YYYY format
Reply with quote

what happened when You looked at the manual to find the possible formats for
CHAR(<somedate>, <someformat> )

You got what You asked for ....
Code:
CHAR(POL_EFF-DT,USA)

returned the date in the USA format MM/DD/YYYY
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Oct 17, 2014 11:06 am    Post subject: Reply to: To convert date to DD/MM/YYYY format
Reply with quote

Thanks Enrico for the reply.
The format I was looking for is DD/MM/YYYY.

When I have put USA, I got the format only as MM/DD/YYYY.

Thanks
Vinu
Back to top
View user's profile Send private message
kameswaramanoj.Akella

New User


Joined: 28 Mar 2013
Posts: 7
Location: india

PostPosted: Fri Oct 17, 2014 12:51 pm    Post subject:
Reply with quote

If you are OK with period(.) as a delimiter, try 'EUR' format instead of 'USA'.
Code:
CHAR(POL_EFF-DT,EUR)

Else, try concatination(||) with SUBSTR function or concatination with date functions(DAY, MONTH and YEAR).
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 129
Location: India

PostPosted: Fri Oct 17, 2014 2:36 pm    Post subject:
Reply with quote

Thanks.
Yes I have made us of

Code:
SUBTSR(CHAR(date,iso),9,2) || '/' ||
SUBTSR(CHAR(date,iso),6,2) || '/' ||
SUBTSR(CHAR(date,iso),1,4)

Output (mentioning only date part)
20/12/2013
12/07/2013
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Oct 17, 2014 11:33 pm    Post subject:
Reply with quote

Or just flip flop ,,

Code:
select substr(main.date,4,3)||substr(main.date,1,2)||substr(main.date,6,5)
 from (
  select
 CHAR(current date,USA) as date 
  from sysibm.sysdummy1) main
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Sun Oct 19, 2014 2:12 pm    Post subject:
Reply with quote

Other option is:
Code:
SELECT DAY(POL_EFF_DT)||'/'||MONTH(POL_EFF_DT)||'/'||YEAR(POL_EFF_DT)
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Oct 20, 2014 5:30 pm    Post subject:
Reply with quote

Code:
varchar_format(pol_eff_dt,'DD/MM/YYYY')
would be to easy, I guess
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 How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
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
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts convert PD to ZD ram_vizag DFSORT/ICETOOL 5 Sat Nov 05, 2016 3:06 am


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