DB2 Date format

Question

I just want to format current date into yyyymmdd in DB2.

I see the date formats available, but how can I use them?

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_datetimetimestamp.htm

SELECT CURDATE() FROM SYSIBM.SYSDUMMY1;

I dont see any straightforward way to use the above listed formats.

Any suggestion?

bhamby · Accepted Answer · 2019-09-19T14:05:25.067

48

SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'YYYYMMDD')
FROM SYSIBM.SYSDUMMY1

Should work on both Mainframe and Linux/Unix/Windows DB2. Info Center entry for VARCHAR_FORMAT().

edited Sep 19 '19 at 14:05

answered Apr 25 '12 at 18:18

bhamby

15,112
1
45
66

that works . thanks .. can you also tell how can i substarct days from the above date ........................................................ like CURDATE() - ( 3 day ) – zod Apr 25 '12 at 18:49
1

It looks like you already have it. :) `CURRENT TIMESTAMP - 3 DAY` – bhamby Apr 25 '12 at 19:05
1

Keep in mind once converted the result may include blanks to the right of values, depending on the db2 version. The column result will be 255 long. – Abel C Apr 06 '18 at 16:07

score 4 · Answer 2 · answered Apr 18 '13 at 19:11

4

One more solution REPLACE (CHAR(current date, ISO),'-','')

answered Apr 18 '13 at 19:11

Joshua Balan

143
1
2

score 3 · Answer 3 · edited May 10 '16 at 17:35

3

select to_char(current date, 'yyyymmdd') from sysibm.sysdummy1

result: 20160510

edited May 10 '16 at 17:35

Paul Roub

36,322
27
84
93

answered May 10 '16 at 17:15

bill_texas-rulz

49
1

The query not working. It return : SQL0440N No authorized routine named "TO_CHAR" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884 – Ian Dec 14 '17 at 03:37
Works here - shortest and best solution for me. Cited from DB2 docs: _The TO_CHAR scalar function is a synonym for the VARCHAR_FORMAT scalar function._ – not2savvy Mar 03 '20 at 12:59

score 1 · Answer 4 · answered Apr 25 '12 at 17:59

1

This isn't straightforward, but

SELECT CHAR(CURRENT DATE, ISO) FROM SYSIBM.SYSDUMMY1

returns the current date in yyyy-mm-dd format. You would have to substring and concatenate the result to get yyyymmdd.

SELECT SUBSTR(CHAR(CURRENT DATE, ISO), 1, 4) ||
    SUBSTR(CHAR(CURRENT DATE, ISO), 6, 2) ||
    SUBSTR(CHAR(CURRENT DATE, ISO), 9, 2)
FROM SYSIBM.SYSDUMMY1

answered Apr 25 '12 at 17:59

Gilbert Le Blanc

50,182
6
67
111

thanks .. but is this the only way to do it? then what is the use of formats .... DATE_K YYYYMMDD ?? – zod Apr 25 '12 at 18:12
I couldn't get the ALTDATE function to work on my DB2. What I posted in my answer works. – Gilbert Le Blanc Apr 25 '12 at 18:22

score 1 · Answer 5 · edited Jul 25 '12 at 07:07

1

Current date is in yyyy-mm-dd format. You can convert it into yyyymmdd format using substring function:

select substr(current date,1,4)||substr(current date,6,2)||substr(currentdate,9,2)

edited Jul 25 '12 at 07:07

Tisho

8,320
6
44
52

answered Jul 24 '12 at 18:07

albin

11
1

DB2 Date format

5 Answers5

Linked