Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Difference between SYSDATE and TO_DATE
two problems
1) SYSDATE includes the time
2) BETWEEN requires values in low/high order
when you do SELECT SYSDATE FROM DUAL, the time is being returned ,but not displayed in SQL*Plus
this is because SQL*Plus uses the database's default date format (NLS_DATE_FORMAT parameter) to convert the internal format to a string for display. since the default format is DD-MON-RR, the time element is hidden
so what happened?
your first query took string literals and converted them to the internal date datatype, using the default database format (not a good idea, more on this later)
since neither your string literals nor the default format contain a time
element, your first search was really
between [28-OCT-2003 00:00:00] and [29-OCT-2003 00:00:00] -- or midnight to
midnight
your second query does not rely on any default date conversions, so it was
really doing something like
between [29-OCT-2003 14:01:22] and [28-OCT-2003 14:01:22]
the 2nd query will never return rows, because the high value comes first in
a BETWEEN comparison
if you switch the values, the effective query becomes something like
between [28-OCT-2003 14:01:22] and [29-OCT-2003 14:01:22]
which will likely never return the same rows as the first query -- it will
either
a) return rows for 29-OCT-2003, if all stored dates have no time element
(i.e., midnight)
b) return rows whose dates have a time element and fall between the more
precise time values supplied -- ie., within the prior 24 hours from the
moment the query is run
solution?
also beware that BETWEEN is inclusive -- so when working with truncated dates, between two consecutive dates will return rows that match either date -- if you are working with truncated dates, just use '=',
"Tom" <tomNOSPAM_at_teameazyriders.com> wrote in message
news:1EVnb.5230$_36.47992857_at_news-text.cableinet.net...
> 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
> ----------------------------------------- -------- ----------------------
--Received on Wed Oct 29 2003 - 15:18:11 CST
> ----
> 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
>
>