|
View previous topic :: View next topic
|
| Author |
Message |
manugeorge2004
New User
Joined: 17 Jun 2009
Posts: 38
Location: Denver
|
|
|
|
Hi
I have a column in a DB2 table which is of DATE datatype.
The value stored is 2015-12-02.
I need to write a SELECT query which I can run using IKJEFT01 which will format it as '20151202'
I used
SELECT CHAR(YEAR(ONLINE_DATE))||
CHAR(MONTH(ONLINE_DATE))||
CHAR(DAY(ONLINE_DATE)).
But it returned me
2015 12 2
Is there anyway to achieve this. I tried using SUBSTR. But it was throwing some error. Can we use SUBSTR on a DATE column
Any advice is appreciated |
|
| Back to top |
|
 |
manugeorge2004
New User
Joined: 17 Jun 2009
Posts: 38
Location: Denver
|
|
|
|
Please ignore this. Below worked
SELECT SUBSTR(CHAR(ONLINE_DATE),1,4),
SUBSTR(CHAR(ONLINE_DATE),6,2),
SUBSTR(CHAR(ONLINE_DATE),9,2) |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Easy,
| Code: |
| select replace('2015-12-02','-','') from sysibm.sysdummy1 |
|
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| Quote: |
| But it was throwing some error. |
No it didn't. Why? Because nothing on the mainframe "throws" an error. You could have said that it errored in the same manner - but it was not an error, just the wrong output. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
You could also use:
| Code: |
SELECT VARCHAR_FORMAT(my_date,'YYYYMMDD')
FROM my.table |
|
|
| Back to top |
|
|
|
|
