Re: date format

On Thu, 18 Oct 2001 01:13:29 GMT, Steve . <syarbrou_at_nospam.enteract.com> wrote:

>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, where say:
>
>select ins_date from docs
>where ins_date < '23-MAR-2001';
>
>Thanks.
>
>Steve
>
>newsgroup replies preferred. Remove nospam when replying thru email.

1 the date is stored as a 7-byte column, 1 byte per element 2 the best way, honestly, to do this is *NOT* to rely on *ANY* default I had a situation once where the developers used a date default value without mask. Their NLS_DATE_FORMAT was different from the server one. It was a table with many children and I had to import the complete schema. The import of almost every individual table failed because of their dependency of the master table.
If you want to use a default, always use MM instead of MON, as MM is language independent.

Hth

Hth

Sybrand Bakker, Senior Oracle DBA Received on Thu Oct 18 2001 - 13:22:17 CDT