CODEWITHSUNDEEP
sqldatedb2date-format
zod
zod

Reputation: 12417

DB2 Date format

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?

Upvotes: 24

Views: 256923

Answers (5)

bhamby
bhamby

Reputation: 15450

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

Upvotes: 50

bill_texas-rulz
bill_texas-rulz

Reputation: 59

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

result: 20160510

Upvotes: 4

Joshua Balan
Joshua Balan

Reputation: 143

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

Upvotes: 4

albin
albin

Reputation: 11

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)

Upvotes: 1

Gilbert Le Blanc
Gilbert Le Blanc

Reputation: 51445

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

Upvotes: 1

Related Questions