Re: Date Columns

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Fri, 22 Oct 1999 10:23:31 -0700
Message-ID: <38109D93.7886CDDD@vnl.nl>

Jason Judge wrote:

> Date columns (or DATE datatypes) do not have a format since they are a
> native date type. The format only comes into being when displaying the date.
> There is a default format - defined in one of the database initialisation
> files - that is used if you do not specifiy a format. This is normally
> DD-MON-YY, though more often than not a DBA will set it to DD-MON-RR or
> DD-MON-RRRR to ensure better Y2K compliency.
>
> Use the TO_CHAR and TO_DATE functions to convert between a date datatype and
> a character datatype.
>
> So TO_CHAR(DateColumn, 'DD/MM/YYYY') will give you your format when you wish
> to select the value out of your table.
>
> Regards,
>
> Jason Judge
>
> Andreas Köster <AKoester_at_DerPatriot.com> wrote in message
> news:3806C377.547A8B6E_at_DerPatriot.com...
> > The standard format for the "DATE" datatype is "01-DEC-99". I think its
> > the internal format that oracle uses.
> > You can set the environment variable "NLS_DATE_FORMAT" to "MM/DD/YYYY".
> > Then even an "select sysdate from dual" should bring you the wanted
> > format.
> >
> > Andreas Köster
> >
> >
> > Brandon Duncan schrieb:
> >
> > > Does anyone know the correct syntax to get a Date Column in the format
> > > 'MM/DD/YYYY' (01/01/1999)?
> > >
> > > I'm using
> > > Create Table XTable (
> > > DateColumn DATE (10, 'MM/DD/YYYY'),
> > > ......
> > >
> > > I get an error message, 'Missing right parenthisis."
> > >
> > > Any ideas?
> > >
> > > Brandon
> >

Beware! Internal routines (like name_in) do use a default format of DD-MON-YY!!!

So, unless you specifically code these, using 4 digit year format masks, you may

still have Y2K problems, despite a 4 digit "default" date format

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle

Work:                                Home:
----------------------------------   ----------------------------
Inter Access V&L                     Hunzestraat 4
Palatijn 3, 7521 PN Enschede         7555 WB Hengelo
PoBox 545, 7500 AM Enschede          (31)074-2425046

053-4341500 Received on Fri Oct 22 1999 - 12:23:31 CDT