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
 
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: 151
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: 10360
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: 151
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: 151
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: 1819
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: 1248
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: 1281
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 Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Can EDCICONV convert EBCDIC to ASCII... natt.sut IBM Tools 3 Fri Nov 17, 2017 8:46 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Format Last Record differently from r... Learncoholic DFSORT/ICETOOL 5 Tue Oct 24, 2017 12:01 pm
No new posts Format issues using XMITIP MSGDD ssdjp1 CLIST & REXX 5 Mon Oct 23, 2017 8:47 pm

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