Re: Formatting dates on insert or update - Help required please

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.

.

Relevant Pages

  • Re: Here is my full program excluding the search and delete functions
    ... There is a mismatch between your format specifications and the ... &ID has type pointer to array of 50 int, ... %s requires a char*. ... >empty, empty); ...
    (comp.lang.c)
  • Re: structure
    ... > char a:4; ... if the compiler does not support ... to match an internal data structure to some external data format ... as the internal representation. ...
    (comp.lang.c)
  • "Type-safe" sprintf
    ... I have invented a simple sprintf-like function aimed at replacing sprintf. ... Char* buffer; ... Might be the format string ... feeder& operator% ...
    (microsoft.public.vc.language)
  • Re: "Type-safe" sprintf
    ... I have invented a simple sprintf-like function aimed at replacing sprintf. ... Char* buffer; ... Might be the format string ... feeder& operator% ...
    (microsoft.public.vc.language)
  • Re: Watcom vs. gcc/icl/cl difference
    ... fputs((const char *)&buffer, echoFP); ... static void out(int opt, const char * format, ...) ... /* Invalid flags given? ...
    (comp.lang.c)