Format date to MM/DD/YYYY

[ermagarden]

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

I want to display them as MM/DD/YYYY

So I used

Select TO_CHAR(mydate,'MM/DD/YYYY')

but get an error that TO_CHAR in *LIBL type *N not found.

I found this http://www.ibm.com/developerworks/db.../0211yip3.html

Tried VARCHAR_FORMAT as well. Tried select Date(mydate) as well...

any ideas?

[kpmac]

Re: Format date to MM/DD/YYYY

The article you were referencing is for DB2 windows/Linux.
Try this instead.

Select 
         Char( Date('1978-01-27', USA ) ) 
From 
         Sysibm.Sysdummy1 

[jamief]

Re: Format date to MM/DD/YYYY

okay thats way better than mine......... Yewd the man

A          R DATESR                                             
A            ISO             L         TEXT('ISO DATE')         
A            CYMD           7  0       TEXT('CYMD DATE')        
A            YMD            6  0       TEXT('YMD DATE') 

      *
      //
      // Variable Definition
      //
     d stringout       s             30
      *

      /Free
       exec sql  set option commit=*none,datfmt=*iso;

        //--------------------------------------------------------
        // MAIN PROGRAM
        //--------------------------------------------------------

        exec sql SELECT DAYNAME(Iso)   || ', ' ||
                        MONTHNAME(Iso) || ' ' ||
                 DAY(Iso) || ', ' || YEAR(Iso)   INTO
                 :Stringout from dates;



           if StringOut <> *blanks;
             dsply Stringout;
           else;
             Stringout = 'Invalid Date Entered';
             dsply Stringout;
           endif;

        exec sql SELECT MONTH(Iso)   || '/' ||
                        DAY(Iso)     || '/' ||
                        YEAR(Iso)   INTO
                 :Stringout from dates;



           if StringOut <> *blanks;
             dsply Stringout;
           else;
             Stringout = 'Invalid Date Entered';
             dsply Stringout;
           endif;

              *inlr = *on;

      /End-Free 

[ermagarden]

Re: Format date to MM/DD/YYYY

I just ran

Select 
         Char( Date('1978-01-27', USA ) ) 
From 
         Sysibm.Sysdummy1

and I get the msg: column USA not specified in tables.

Tried

Select 
         Char( Date('1978-01-27', 'MM/DD/YYYY' ) ) 
From 
         Sysibm.Sysdummy1

and

Char( Date('1978-01-27', MM/DD/YYYY') )

but didnt work..what am I missing?

[kpmac]

Re: Format date to MM/DD/YYYY

Thats because I typed it wrong.

Select
Char( Date( '1978-01-27') ,USA )
From
Sysibm.Sysdummy1 

[ermagarden]

Re: Format date to MM/DD/YYYY

thanks, that worked. Was googling when u posted.

thanks again

[jamief]

Re: Format date to MM/DD/YYYY

@#%@#%@#%@#% Business analysts !!!!!

[kpmac]

Re: Format date to MM/DD/YYYY

Thats Senior Business Analyst.

Hey, I am getting rusty. I am too busy creating power points and word docs.

Biznitz Analitz.

[ermagarden]

Re: Format date to MM/DD/YYYY

been off to my SQL Server and .Net world but now back to AS400 stuff

[jamief]

Re: Format date to MM/DD/YYYY

Post some pretty documents so we can all EWWW and AHHHHH!

Nice to have you posting....Have a great weekend!

[ermagarden]

Re: Format date to MM/DD/YYYY

anyway to have it as "dd/mm/yyyy" as well?

Looked at this as400 book i have (just remembered i have a book ) and options are ISO, USA,EUR, JIS and LOCAL

noway to have it as dd/mm/yyyy?

[jamief]

Re: Format date to MM/DD/YYYY

try using *EUR

if you dont like the "." then

                                             
   exec sql SELECT DAY(Iso)   || '/' ||      
                   MONTH(Iso)     || '/' ||  
                   YEAR(Iso)   INTO          
            :Stringout from dates; 

[ermagarden]

Re: Format date to MM/DD/YYYY

silly me, the EUR is dd.mm.yyyy. Didnt notice it.

Thanks again for your help.

[kpmac]

Re: Format date to MM/DD/YYYY

Now you are cherry picking.
Ha, I didnt notice that either. EUR, who would have thought.

Select

      Right( Char( tDate ), 2 ) ConCat '/' ConCat
      SubStr( Char( tDate ) , 6, 2 ) ConCat '/' ConCat
      Left( Char( tDate ) ,4 )

From(
Select
       Date('1978-01-27')as tDate
From
       Sysibm.Sysdummy1
) x 

[jamief]

Re: Format date to MM/DD/YYYY

Did I say everyone involved in this thread ROCKS!
wait till Birgitta gets here and spanks us all and tells us the right way to do it

jamie