Re: [REPOST] The "TO_DATE" Function Question

From: Chris Doran <chris_doran_at_postmaster.co.uk>
Date: 2 Jan 2002 17:29:49 -0800
Message-ID: <948f0720.0201021729.72ca37a1_at_posting.google.com>


aleatory_at_hotmail.com (aleatory) wrote in message news:<a68a4ee0.0201011141.596efbf2_at_posting.google.com>...
>
> I have a question on the "TO_DATE" function.
>
> I'm learning Oracle SQL using SQL Plus. The following
> is from the book "Oracle SQL Interactive Workbook."
>
> | SQL> SELECT last_name, registration_date,
> | 2 TO_CHAR ( registration_date, 'MM/DD/YYYY' )
> | 3 FROM student
> | 4 WHERE registration_date = '22-JAN-99'
> | 5 /
>
> After executing this, the program displays:
>
> | no rows selected
>
> When I execute the following code, the information
> is there. With the previous SQL code, no rows get
> displayed:
>
> | SQL> SELECT registration_date
> | 1 FROM student
> | 2 /
> |
> | REGISTRAT
> | ---------
> | 22-JAN-99
> | 22-JAN-99
> | 23-JAN-99
> | etc...
>...

Your question raises a number of ugly heads around use of DATEs in Oracle.

As Franz says, DATE works to seconds, but SQL*Plus by default shows only the date portion. To see the full date/times in your student table, use SELECT TO_CHAR(registration_date,'DD/MM/YYYY HH24:MI:SS')...;

Claire is also right (for some but not all versions of Oracle/SQL*Plus), that by default 22-JAN-99 means 2099, so you will make your life easier if you change the examples in the book to this millennium! Again, depending on version and default format setup, '22-JAN-1999' may not even be accepted.

In your first SELECT command, '22-JAN-99' actually means '22-JAN-2099 00:00:00' (or maybe if you're lucky '22-JAN-1999 00:00:00'), and even if you get the millennium right, you probably don't have any rows timed at midnight unless whatever enters the rows doesn't add a time. One way to SELECT everything created on the 22nd January is to do ... WHERE registration_date>='02-JAN-99' AND registration_date<'03-JAN-99';

If you issue the command:
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'; SQL*Plus will show date+time in SELECT and also accept -99 as -1999, but there seem to be other problems like insisting you give the time in every DATE string. Again, this seems to be dependent on the version of Oracle/SQL*Plus.

I hope that doesn't make you even more confused than you are already :-).

Chris Received on Thu Jan 03 2002 - 02:29:49 CET

Original text of this message