[Info-Ingres] Formatting dates on insert or update - Helprequired please

[Richard Harden]

 Hi All,

A big thank you to all who provided suggestions and/or solutions both
on-line and off-line


Thanks esp. Gary and Dave

Dave's solutions (off-line) was perhaps the more elegant and simpler. 

select right(varchar(100+date_part('day','today')),2) +   
case
 when date_part('month','today')= 1 then '-jan-' 
 when date_part('month','today')= 2 then '-feb-' 
 when date_part('month','today')= 3 then '-mar-' 
 when date_part('month','today')= 4 then '-apr-' 
 when date_part('month','today')= 5 then '-may-' 
 when date_part('month','today')= 6 then '-jun-' 
 when date_part('month','today')= 7 then '-jul-' 
 when date_part('month','today')= 8 then '-aug-' 
 when date_part('month','today')= 9 then '-sep-' 
 when date_part('month','today')= 10 then '-oct-'
 when date_part('month','today')= 11 then '-nov-'
 when date_part('month','today')= 12 then '-dec-'
end  +
 varchar(date_part('year','today'));


(Well to me anyway as at least I sort of understood it at first look)

But Gary's I'm going to have to work through carefully to make sure I
understand how/why it works

It might be that there is less overhead in one or the other, but without
testing I won't open mouth and put both feet in ;(

In any case, I appreciate all help provided

Thanks again.



Cheers

Richard

/**********************************\
| New Zealander, leading the world |
\**********************************/

-----Original Message-----
From: info-ingres-bounces at kettleriverconsulting.com
[mailto:info-ingres-bounces at kettleriverconsulting.com] On Behalf Of
ghingres at yahoo.co.uk
Sent: Tuesday, 7 August 2007 6:39 a.m.
To: info-ingres at kettleriverconsulting.com
Subject: Re: [Info-Ingres] Formatting dates on insert or update -
Helprequired please

Hi Richard,

    Hope you've got your Jandals on for this one... :-)

    Why not use the simple method of SQL, here are two examples for DD-
MMM-YYYY and YYYYMMDD etc...

SELECT
    RIGHT('00'+VARCHAR(date_part('DAY',hht_start_date)),2)+'-'+
    RIGHT(LEFT('JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,',
               (INT2(date_part('MONTH',hht_start_date)) * 4) - 1
              ),3)+'-'+
    RIGHT('0000'+VARCHAR(date_part('YEAR',hht_start_date)),4) AS
start_date1,

    RIGHT('0000'+VARCHAR(date_part('YEAR',hht_start_date)),4)+
    RIGHT('00'+VARCHAR(date_part('MONTH',hht_start_date)),2)+
    RIGHT('00'+VARCHAR(date_part('DAY',hht_start_date)),2) AS start_date2,

   Just swap hht_start_date for 'today' or your local variable etc...

   Enjoy

Gary

_______________________________________________
Info-Ingres mailing list
Info-Ingres at kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres