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?

Ursel

Answer 1

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().

Caprice

that works . thanks .. can you also tell how can i substarct days from the above date ........................................................ like CURDATE() - ( 3 day ) – Ursel

It looks like you already have it. :) CURRENT TIMESTAMP - 3 DAY – Caprice

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. – Brufsky

Answer 2

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

Birmingham

Answer 3

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

result: 20160510

Nit

The query not working. It return : SQL0440N No authorized routine named "TO_CHAR" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884 – Sellingplater

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. – Anacoluthia

Answer 4

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

Blent

thanks .. but is this the only way to do it? then what is the use of formats .... DATE_K YYYYMMDD ?? – Ursel

I couldn't get the ALTDATE function to work on my DB2. What I posted in my answer works. – Blent

Answer 5

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)

Crossman