| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Dates
A copy of this was sent to Moore <rlmoore_at_purdue.edu>
(if that email address didn't require changing)
On Thu, 24 Feb 2000 07:24:10 -0500, you wrote:
>Greetings.
>
>I have been working with Oracle 7.3 after spending many months w/ SQL
>Server 6.5 and 7.0.
>
>May I say that date formats/functions etc in Oracle, well frankly, they
>suck.
>
>Any sure fire way to compare the value of a date field?
>
>I.E.
>
>The following
>
>SELECT FIELD1
>FROM TABLE
>WHERE ADATEFIELD >= TO_DATE('16-SEP-99', 'DD-MON-YY')
>
>will return different results then
>
>SELECT FIELD1
>FROM TABLE
>WHERE ADATEFIELD >= TO_DATE('16-SEP-1999', 'DD-MON-YYYY')
What century are we in?
ops$tkyte_at_8i> select to_date( '16-sep-99', 'dd-mon-yy' ) from dual; TO_DATE('16-SEP-99',
ops$tkyte_at_8i> select to_date( '16-sep-1999', 'dd-mon-yyyy' ) from dual; TO_DATE('16-SEP-1999
ops$tkyte_at_8i> select to_date( '16-sep-99', 'dd-mon-rr' ) from dual; TO_DATE('16-SEP-99',
2099 != 1999, thats why they are different -- you are using two totally different dates.
Don't use 2 character years, use 4 digits.
I think after you learn the formats, you'll find them pretty flexible and predicable. Don't use 2 char years and things work best.
RR and RRRR implement nice 50 year sliding 'windows', maybe they can be of use. But I would ask you:
to_date( '16-sep-50' ) -- what should that be? 1950 or 2050??? (it'll be 1950 but '16-sep-49' with RR would be 2049). It is always best to be explicit in what you are asking.
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Feb 24 2000 - 07:56:48 CST
 |
 |