Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Difference between SYSDATE and TO_DATE

Re: Difference between SYSDATE and TO_DATE

From: mcstock <mcstock_at_enquery.com>
Date: Wed, 29 Oct 2003 16:18:11 -0500
Message-ID: <oLydnUsgQJIksz2iRVn-gg@comcast.com>


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?

  1. if you do not have time values in the columns, use TRUNC(SYSDATE) to truncate the time to midnight (00:00:00)
  2. do not, under any circumstances, ever, at all, in your wildest dreams, ever, ever rely on the default date format -- always use an explicit format with TO_DATE and TO_CHAR, or use the ALTER SESSION command to explicitly set the default format for you session

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 '=',



Mark C. Stock
www.enquery.com
(888) 512-2048

"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
> ----------------------------------------- -------- ----------------------

--

> ----
> 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:18:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US