Re: [REPOST] The "TO_DATE" Function Question

From: aleatory <aleatory_at_hotmail.com>
Date: 5 Jan 2002 05:56:39 -0800
Message-ID: <a68a4ee0.0201050556.28193478_at_posting.google.com>


Hi Claire, Franz, Cliff, and Chris,

You guys gave me great tips on the TO_DATE function. Thanks!

To Chaire:
You're right! After changing the where clause as you described in your post, the SQL Plus program shows the correct result set.

To Franz:
Thanks for telling me the TRUNC function. After reading the book, the function truncates the timestamp portion. Indeed, with the tip from Claire, your suggestion also gives me the correst result set.

To Cliff:
Yes, I tried chaning the date format as you suggested, but didn't work out nicely. Perhaps just specifying the two-digit-year notation might not be a good idea.

To Chris:
I really appreciate your wonderful summary on the TO_DATE function! You explained the function well. Your explanation is lots more understandable than the book I learn. (I think you should write an Oracle SQL book, really!)

By the way, does anyone know any good, meaning for "newbies," Oracle SQL book? As you can tell the book I use does not clearly explain the TO_DATE function. (Probably more to come when I keep reading the book.)

Any information is welcomed since I have to pass the Oracle Silver exams...

Thanks in advace! :)

alea

chris_doran_at_postmaster.co.uk (Chris Doran) wrote in message news:<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 Sat Jan 05 2002 - 14:56:39 CET

Original text of this message