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

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@xxxxxxxxxxxxxxxxxxxxxxxxx
[mailto:info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx] On Behalf Of
ghingres@xxxxxxxxxxx
Sent: Tuesday, 7 August 2007 6:39 a.m.
To: info-ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres


.

Relevant Pages

  • Re: Large unusable area on NTFS volume
    ... Kerry, I think I am far past the ppint of diminishing returns. ... have put into simply formatting and reinstalling everything. ... >> stubborn and I want to find a more elegant solution to the problem. ...
    (microsoft.public.windowsxp.help_and_support)
  • dlookup and sql
    ... creates a temp query using sql, then using dlookup to get data, eg. using sql ... using dlookup to return the count. ... Dim qry As QueryDef ... Is there a more elegant way to pass the result from a sql statement straight ...
    (microsoft.public.access.queries)
  • Re: Berechnete Felder archivieren
    ... >> Musst du per SQL oder per Recordset machen. ... "Elegant" ist ein bissel Geschmacksfrage. ... SQL = Schnelle Qualitäts-Lösung ...
    (microsoft.public.de.access)
  • Re: maxrecords property doesnt work for me
    ... elegant. ... You want to bone up on SQL. ... I only do if for some reason cannot be done. ...
    (microsoft.public.data.ado)