Format date to MM/DD/YYYY

[B.Hauser]

Re: Format date to MM/DD/YYYY

Hi,

I'm doing a select mydate from whereever and dates are displayed as YYYY-MM-DD

Before you try all these more or less complicated solutions you already got, just a question. Where or how do you want to display dates?

If you are using interactive SQL (STRSQL), just do the following steps:

• Press F13=Services
• Select 1. Change Session Attributes
• Change the date format from *ISO to *EUR

If you are working with iSeries Navigator's Run an SQL Script:

• Click Connection
• Click JDBC-Setup
• Click Format
• Change the date format to the format you want

Other interfaces may have comparable possibilites to change the date format.
A date is always stored as 4 byte binary value repesenting the calculated days from 01/01/0001. A date format is used to make this cryptic date readable.

But the date separators for the predefined date formats ISO, USA, EUR and JIS are fixed and cannot be changed.
ISO: YYYY-MM-DD
USA: MM/DD/YYYY
EUR: DD.MM.YYYY
JIS: YYYY-MM-DD

I you really need to show the European date with slashes as separators, you have to convert the date into the character representation of the date and replace the date separators after:

select translate(char(current_date, EUR), '/', '.')
   from sysIbm/sysdummy1; 

Birgitta

[bryce4president]

Re: Format date to MM/DD/YYYY

NOW THAT IS AN ANSWER!

so smart that birgitta

[soup_dog]

Re: Format date to MM/DD/YYYY

Here is a different wrinkle. I have an 8 digit number containing a date in YYYYMMDD format. Anyway to read that number in (using SQL) and reformat it into a 6 digit number in MMDDYY format?

I am digging through the manual right now, but was hoping someone had a quick answer.

[jamief]

Re: Format date to MM/DD/YYYY

This isnt exactly what you asking but it will get you in the right field..

Select OhNum,
   Substr( Digits( OhDtOr ), 1, 4 )|| '-' || 
   Substr( Digits( OhDtOr ), 5, 2 )|| '-' || 
   Substr( Digits( OhDtOr ), 7, 2 ) 
   As OrderDate, 
   Date( Substr( Digits( OhDtOr ), 1, 4 ) 
    || '-' || 
      Substr( Digits( OhDtOr ), 5, 2 ) 
       || '-' || 
      Substr( Digits( OhDtOr ), 7, 2 ) ) 
   + 10 Days As EstShipDate 
  From OrdHdr 

[littlepd]

Re: Format date to MM/DD/YYYY

"right field"?!? But I want to play in center field!

[FaStOnE]

Re: Format date to MM/DD/YYYY

There are times like this that we're just happy he's in the RIGHT BALLPARK!

[jamief]

Re: Format date to MM/DD/YYYY

have i told you how much I like cheese ?

I guess Ill save that for another day!


happy weekend ALL

jamie