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

[s.anderson.au at gmail.com]

On Aug 8, 11:51 pm, --CELKO-- <jcelko... at earthlink.net> wrote:
> >>  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 [sic]  in the format dd-mmm-yyyy <<
>
> This is one of the many differences between rows and records.  A
> column in a row has a data type and domain, while fields do not.
> Temporal data has an internal representation that has nothing to do
> with the display.  In fact the **only** format allowed in Standard
> ANSI/ISO SQL is "yyyy-mm-dd" and that is all you should be using.
>
> Using those local format is a total violation of a tiered architecture
> and good SQL programmers avoid them.  Let the application programmers
> worry about display; we are in charge of data and we follow
> standards!
>
> That said, another trick for validation of input strings is to set up
> a table with your various formats and do a look-up to convert them.
> Usually a 100 years is enough.  Uses an OUTER JOIN to find invalid
> dates in either format.
>
> CREATE TABLE FixBadDates
> (sql_date CHAR (10) NOT NULL,
>   bad_date CHAR (10) NOT NULL,
>  etc)
>
> INSERT INTO FixBadDates VALUES ('2007-01-01', '01-jan-07', ..);
>
> The JOIN to the FixBadDates table can be faster than using row at a
> time string manipulations that cannot get to an index or hash.  And
> this is portable, too.

That's an interesting approach CELKO using the FixBadDates table,
though I am puzzled why you need to worry about how date information
is stored in a table.  Isn't it more appropriate for the application
to worry about/manage the displaying of dates in the 'correct'
format?  With Ingres doing such a wonderful job of storing dates, via
the date datatype, I don't understand why one would choose to store
date values as character strings?

Of course I could be completely missing the point!

Go the Kiwis!  Was does Orcon use Ingres for, just curious?