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

[martin.bowes at ctsu.ox.ac.uk]

Hi Karl,

Yep that looks undocumented and the set statement is not accepted 
inside a dbms procedure - which is a bummer as it looks kinda useful.

Marty

> At 10:10 PM +1200 8/2/07, Richard Harden wrote:
> >Hi all,
> >
> >
> >I have a small database procedure that on insert into tableA, it
> >inserts a related row into tableB Where currently it uses
> >
> ><snipped for brevity of course>
> >Insert into std_activity_references (:std_activity_no,'WP Rev
> >Date',char(date('today'),10) ) <snip...>
> >
> >Where because ii_date_format is set to Multinational4, the dates thus
> >inserted are in the form dd/mm/yyyy.
> >
> >Is there any simple way of formating the dates so that it is inserted
> >into the record in the format dd-mmm-yyyy
> 
> Dates don't have any format when stored.  They are stored as a
> binary structure giving the date, and time as GMT if there's a
> time part.
> 
> Dates are displayed according to the II_DATE_FORMAT setting,
> and at present that's almost your only option.  You can
> also cast the dates to varchar within an SQL query, and
> when that's done, the conversion is under the control of
> the SET DATE_FORMAT statement, which might be undocumented;
> I haven't looked.  The server date format is initialized to
> the II_DATE_FORMAT when you start a session, but you can
> change it with SET DATE_FORMAT.  That only affects date
> conversions done within the server, though, not outside
> conversions which are done when you retrieve a date as a
> date type.
> 
> Karl
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres at kettleriverconsulting.com
> http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
>