Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

colezpapa3

Programmer
Dec 8, 2007
73
US
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) ....
 
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
 
How about this ugly bastard:
Code:
  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.
 
This one also works:

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

 
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:
 
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:
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top