How do you change the date format from YYYYMMDD to MM/DD/YYYY

[colezpapa3]

I have a date in DB2 format 20080313, I need to build it as month / day / year. How can I do it in DB2 SQL?

Something like Month(CURRENT DATE) + '/' + Day(CURRENT DATE) + '/' + Year(CURRENT DATE) ....

 

[colezpapa3]

I think I figured it out....

SELECT Case dayofweek(CURRENT DATE) When 2 then Char((CURRENT_DATE-3 days),USA) Else Char((CURRENT_DATE-1 day),USA) End FROM sysibm.sysdummy1

 

[Truusvlugindewind]

How about this ugly bastard:

  SUBSTR(CHAR(DEC(DAY  (V0.DATUM_AANMAAK_OB),2 )),2,2) || '-' || 
  SUBSTR(CHAR(DEC(MONTH(V0.DATUM_AANMAAK_OB),2 )),2,2) || '-' || 
  SUBSTR(CHAR(DEC(YEAR (V0.DATUM_AANMAAK_OB),4 )),2,4)

I use this to retrieve a date from a table in (dutch) printable format. The DEC trick is added to force a leading zero.

 

[mtl2302]

This one also works:

substr (char (reacdt),5,2) ||'/'||
substr (char (reacdt),7,2) ||'/'||
substr (char (reacdt),1,4)

 

[HanbingL]

Format MM/DD/YYYY is USA format. To change the format, you can bind the collection of db2 utility packages to use a different date format. The formats supported are:

DEF Use a date and time format associated with the territory code.
EUR Use the IBM standard for Europe date and time format.
ISO Use the date and time format of the International Standards Organization.
JIS Use the date and time format of the Japanese Industrial Standard.
LOC Use the date and time format in local form associated with the territory code of the database.
USA Use the IBM standard for U.S. date and time format.


db2 bind @db2ubind.lst datetime USA blocking all grant public


see:

http://www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html

 

[MarcLodge]

As this thread seems to have revived itself, I'll give my slant on it. There are a number of formats which can be specified from within the SQL without having to resort to binds packages or complex substr concatenations. Try:

SELECT CHAR(CURRENT_DATE,ISO)
      ,CHAR(CURRENT_DATE,USA)
      ,CHAR(CURRENT_DATE,EUR)
      ,CHAR(CURRENT_DATE,JIS)
FROM SYSIBM.SYSDUMMY1

on your system and you will see the different standard formats. The one that colezpapa3 required back in March is the USA format.

Apologies for not spotting this thread back in March!

Marc