Thread: DateStyle

Can anyone tell if it is possible (and if yes how) to use a datestyle like:
II-AAA-IIII

where the "I" is an integer character and the "A" is an alpha character. An
example would be the 12th of Febuary 1949 would be displayed as
12-Feb-1949. I cannot find anything in the last last 18 months in the
archives and Bruce Momjian's book refers only to styles other than the one
I have asked about.

The reason I'm asking is that building a database to be compatible in
format to an Oracle database. Also it seems to me that whatever digit only
ordering one uses that it can be confusing to an outsider querying a
database unless they know the format whereas the format I'm looking for is
unambiguous.

A possible solution I thought of would be store the date as characters but
I'm not sure if I could date manipulation (like days between dates) using
characters as the data.

Any help would be appreciated.

Mike Withers
University of Western Sydney
Australia

On Fri, Aug 10, 2001 at 02:07:01PM +1000, Mike Withers wrote:
> Can anyone tell if it is possible (and if yes how) to use a datestyle like:
> II-AAA-IIII

to_char should be able to help you there. However, that's for explicit
conversions, not the default format.

> The reason I'm asking is that building a database to be compatible in
> format to an Oracle database. Also it seems to me that whatever digit only
> ordering one uses that it can be confusing to an outsider querying a
> database unless they know the format whereas the format I'm looking for is
> unambiguous.

Well, I always insist on yyyy-mm-dd because it's standard ISO date format
and has certain nice features. It's also unambiguous. (Note, postgres did at
some stage support yyyy-dd-mm which is so stupid I'd like to kill the person
who thought of it).

> A possible solution I thought of would be store the date as characters but
> I'm not sure if I could date manipulation (like days between dates) using
> characters as the data.

Don't store them as strings. To do so means you lose all the cool functions
relating to dates.

# select to_char('10-jul-2001'::date,'dd-Mon-YYYY');
   to_char
-------------
 10-Jul-2001

Is that enough?

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.