Dates

[Moore]

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')

[XXX]

Sure, do you forgot the Y2K problems?
Try this :

SELECT FIELD1
FROM TABLE
WHERE ADATEFIELD >= TO_DATE('16-SEP-99', 'DD-MON-RR')

Let me know how it happend, please.

vaclav...@anfdata.cz

Moore <rlm...@purdue.edu> píse v diskusním
příspěvku:Pine.SOL.4.10.100022...@herald.cc.purdue.edu...

[Thomas Kyte]

A copy of this was sent to Moore <rlm...@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@8i> select to_date( '16-sep-99', 'dd-mon-yy' ) from dual;
TO_DATE('16-SEP-99',
--------------------
16-sep-2099 00:00:00

ops$tkyte@8i> select to_date( '16-sep-1999', 'dd-mon-yyyy' ) from dual;
TO_DATE('16-SEP-1999
--------------------
16-sep-1999 00:00:00

ops$tkyte@8i> select to_date( '16-sep-99', 'dd-mon-rr' ) from dual;
TO_DATE('16-SEP-99',
--------------------
16-sep-1999 00:00:00

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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

[Huberto Kusters]

Hi,

There is a reason for this behaviour.

Try the following sql-statement

select to_char(to_date('16-SEP-99', 'dd-mon-yy'), 'dd-mon-yyyy') from dual;

As you can see, oracle converts you 99 to 2099 and that's ofcourse different
than 1999.

Best regards,

Huberto

Moore <rlm...@purdue.edu> schreef in berichtnieuws
Pine.SOL.4.10.100022...@herald.cc.purdue.edu...