Re: date format

From: Nicholas Carey <ncarey_at_speakeasy.org>
Date: Thu, 18 Oct 2001 04:37:48 -0000
Message-ID: <Xns913DDC087E20Fncareyspeakeasyorg@207.126.101.92>

On 17 Oct 2001, Steve . <syarbrou_at_nospam.enteract.com> spake and said:

> I am trying to figure out what the format a date field is
> being stored in an Oracle 8.0.5 database. Problem is that
> depending on the NLS_DATE_FORMAT setting on the client,
> SQL*PLUS will return the format differently. So two
> questions:
>
> 1. Is there a way to tell the real format the data is being
> stored in.
> 2. what is the best way to run a query against an oracle
> date field and be assured of an accurate comparison...

The data is stored as an Oracle DATE datatype.

The epoch (beginnning) of an Oracle DATE datatype is 1 January 4712 BCE (Before Christian Era.) Its precision is 1 second. Its domain ranges from 1 Jan 4712 BCE 00:00 through 31 Dec 9999 AD 23:59 inclusive.

Oracle has no way to directly specify a date/time literal. You must cast a string or numeric expression to a date with the function to_date(), thusly:

to_date( <expression> , <format-mask> )

There is an implicit cast *if* you specify a string value that meets the default date format specified by NLS_DATE_FORMAT.

The safest date format to use (and least ambiguous) is that specified by ISO 8601 (which has other salutory benefits -- it can be readily collated and it compares as a string for instance. More information about ISO 8601 can be obtained from

http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Basically, the ISO date format meets any of the following patterns:

YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM
YYYY-MM-DD

where the various parts are

Mask Description

----------- YYYY 4-digit year. MM 2-digit month (01-12) DD day of month (01-31, unless modified by the month)

ISO 8601 being designed by committee, there are of course, variations on the above theme. Just to make things confusing.

To specify a full ISO date format in Oracle, you would code something like this:

to_date( :dtNow , 'YYYY-MM-DD HH24:MM:SS' )

Oracle provides teh special ISO masks IYYY and IW (ISO year and ISO week-of-year, but these will probably not give you what you want.

The ISO year is defined as an integral number of calendar weeks, either 52 or 53. ISO calendar weeks are defined as

| A seven day period within a calendar year, starting on a Monday
| and identified by its ordinal number within the year; the first
| calendar week of the year is the one that includes the first
| Thursday of that year.

By examination, this is equivalent to:

the week containing 4 january.
the first week of the year containing a majority of its days in the new year.

Ergo, week 1 may straddle the new year, as may the last week of the year. The ISO year begins on a Monday between 28 December and 3 January and ends on a Sunday between 28 December and 3 January. It's either 364 or 371 days long.

Hope this helps.

--

Received on Wed Oct 17 2001 - 23:37:48 CDT