Re: DAte conversion

On Wed, 28 Nov 2001 00:07:54 +0100, longinus <longinus_at_zeus.polsl.gliwice.pl> a écrit:
>Becase the date type doesn't allow to store dates in 'DD.MM.YYYY'
>format,e.g. '12.03.2001'.

You've got the problem wrong.
A Date column is stored in an internal format to oracle. This is completely independant from the way you want it displayed.

After that, you can convert it to whatever you want.

As an example : you've got a date in the DD.MM.YYYY format. You store is with : to_date('15.08.2001','DD.MM.YYYY')

And you get it back with select to_char(my_date,'DD.MM.YYYY')

The BIG advantage is that if somewhere you want to display it otherwise, you can do select to_char(my_date,'DD-MON-YYYY') !

There can be a problem when using ADO or smthing else 'cause the client may convert 'automatically' in something else than want you want, and if using to_char, it's then invalid when you try to insert. (we had some quirks with VB which tries to force it to a valid date thus sometimes inverting day and month ...)

-> you can specify your preferred format   . at the database creation : NLS_DATE_FORMAT (and NLS_DATE_LANGUAGE if you want to spell the months explicitely)
  . when connecting : set NLS_DATE_FORMAT = 'DD.MM.YYYY' and then 'export NLS_DATE_FORMAT' if you're on a unix system  . add a key in the registry in HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE , KEY : NLS_DATE_FORMAT and the value is the format you want. (you may have to add it in each home if you've got several homes (HOME0 , HOME1 ...)

-- 
Damien