|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
kameswaramanoj.Akella
New User
Joined: 28 Mar 2013
Posts: 7
Location: india
|
|
|
|
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 |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
Other option is:
| Code: |
| SELECT DAY(POL_EFF_DT)||'/'||MONTH(POL_EFF_DT)||'/'||YEAR(POL_EFF_DT) |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| varchar_format(pol_eff_dt,'DD/MM/YYYY') |
would be to easy, I guess |
|
| Back to top |
|
|
|
|
