Re: Difference between SYSDATE and TO

From: Tine Zorko <tine.zorko_at_email.si>
Date: Wed, 29 Oct 2003 22:53:18 +0100
Message-ID: <bnpcse$r21$1@planja.arnes.si>

You should take a second look at your code. If BETWEEN evaluation operator has left AND right arguments then:

1st SELECT:

	left argument < right argument (LESS)
	(28-oct-03)   < (29-oct-03)

2nd SELECT
	left argument > right argument (GREATER)
	sysdate       > sysdate - 1

There is AND logical operator between both arguments. As with most recent programming languages Oracle also evaluates truth of 1st argument and if not true it is logical, 2nd argument makes no sense:

Arg1 AND Arg2	Outcome
1	 1	TRUE, 2nd argument will be considered in evaluation
1	 0	FALSE, 2nd argument will be considered in evaluation
0	 0	FALSE, only 1st argument is evaluated
0	 1	FALSE, only 1st argument is evaluated <- YOUR CASE!!

BETWEEN can be interpreted like:

operand > arg1 AND operand < arg2

Also, be careful with sysdate. Consider this example:

SELECT

     TO_CHAR(SYSDATE, 'DD-Mon-YY HH:MI:SS')"SYSDATE",
     TO_CHAR(TO_DATE('29-okt-03'), 'DD-Mon-YY HH:MI:SS')"TO_DATE",
     TO_CHAR(TRUNC(SYSDATE), 'DD-Mon-YY HH:MI:SS')"TRUNC(SYSDATE)",
     TO_CHAR(TRUNC(TO_DATE('29-okt-03')), 'DD-Mon-YY

HH:MI:SS')"TRUNC(TO_DATE)"
FROM DUAL; SYSDATE TO_DATE TRUNC(SYSDATE)

TRUNC(TO_DATE)------------------ ------------------ 
----------------------------------

29-Okt-03 10:48:51 29-Okt-03 12:00:00 29-Okt-03 12:00:00 29-Okt-03 12:00:00

You can see, sysdate has different time value then to_date. For date comparison you must use TRUNC function. SYSDATE is not same value as TO_DATE('29-Okt-03').

select count(*) from dual where TRUNC(sysdate) = to_date('29-Okt-2003');

COUNT(*)

1 => TRUE select count(*) from dual where sysdate = to_date('29-Okt-2003');

COUNT(*)

0 => FALSE Regards,

Tine Zorko

www.rikom.si
www.pohorje.org

Tom wrote:

> Hi,
>
> Can anyone explain what the difference between these 2 queries is and why
> one returns 0 rows?
>
> SELECT Count(*) FROM EVENTSTREAM WHERE TYPE = 11 AND (TS BETWEEN
> TO_DATE('28-OCT-03') AND TO_DATE('29-OCT-03'));
>
> COUNT(*)
> ----------
> 447
>
> and
>
> SELECT Count(*) FROM EVENTSTREAM WHERE TYPE = 11 AND (TS BETWEEN SYSDATE AND
> SYSDATE-1);
>
> COUNT(*)
> ----------
> 0
>
> and just to check SYSDATE
>
> SELECT SYSDATE FROM DUAL;
>
> SYSDATE
> ---------
> 29-OCT-03
>
> DESC EVENTSTREAM;
> Name Null? Type
> ----------------------------------------- -------- ------------------------
> ----
> TS NOT NULL DATE
> TYPE NOT NULL NUMBER(2)
> PERMID VARCHAR2(40)
> SESSID VARCHAR2(40)
> LODGECODE VARCHAR2(10)
> CUSTCODE VARCHAR2(15)
> BOOKCODE VARCHAR2(15)
> BOOKTRACKNO NUMBER(9)
> CAMPAIGNCODE VARCHAR2(30)
>
> This is on 9.2.0.4 and 8.1.5 they both return the same.
>
> thanks
>
> Tom
>
>
Received on Wed Oct 29 2003 - 15:53:18 CST

Re: Difference between SYSDATE and TO_DATE