Professional Web Applications Themes

Difference between SYSDATE and TO_DATE - Oracle Server

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

  1. #1

    Default Difference between SYSDATE and TO_DATE

    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


    Tom Guest

  2. #2

    Default 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?

    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 cirstances, 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" <com> wrote in message
    news:1EVnb.5230$cableinet.net... 
    AND 
    -- 


    mcstock Guest

  3. #3

    Default Re: Difference between SYSDATE and TO_DATE

    > SELECT Count(*) FROM EVENTSTREAM WHERE TYPE = 11 AND (TS BETWEEN 

    In the above, the date does not include time. So the time is truncated.
    SYSDATE includes both date *and* time. So it is possible to have
    different results because you are passing in different values.

    When you selected SYSDATE from DUAL, it only showed the time because
    that is how your NLS_DATE_FORMAT is set. The time is there, but the
    display just doesn't show it.

    HTH,
    Brian



    --
    ================================================== =================

    Brian Peasland
    peasland.com

    Remove the "remove_spam." from the email address to email me.


    "I can give it to you cheap, quick, and good. Now pick two out of
    the three"
    Brian Guest

  4. #4

    Default Re: Difference between SYSDATE and TO_DATE

    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:
     

    Tine Guest

  5. #5

    Default Re: Difference between SYSDATE and TO_DATE


    Tom,



    You need to be careful when working with date and remember that the date
    functions are available so use them when needed. Also when using the
    date range, allways go from older-date to newer-date not the other way
    around like you had, so try this instead:



    SELECT Count(*)

    FROM EVENTSTREAM

    WHERE TYPE = 11

    AND (TS BETWEEN to_char(sysdate -1, 'DD-MON-YY') AND

    to_char(sysdate, 'DD-MON-YY'));



    Linh


    --
    Posted via http://dbforums.com
    lnvu Guest

  6. #6

    Default Re: Difference between SYSDATE and TO_DATE

     

    Thanks all -

    seems i should avoid using sysdate then and focus on to_date.

    thanks

    Tom


    Tom Guest

  7. #7

    Default Re: Difference between SYSDATE and TO_DATE

    I believe you could use TRUNC(SYSDATE) as well to eliminate the time portion for date only comparisons.

    --
    Al Reid

    "It ain't what you don't know that gets you into trouble. It's what you know
    for sure that just ain't so." --- Mark Twain

    "Tom" <com> wrote in message news:uk.clara.net... 
    >
    > Thanks all -
    >
    > seems i should avoid using sysdate then and focus on to_date.
    >
    > thanks
    >
    > Tom
    >
    >[/ref]


    Al Guest

  8. #8

    Default Re: Difference between SYSDATE and TO_DATE

    no need to avoid sysdate -- just be aware of what it does

    "Tom" <com> wrote in message
    news:uk.clara.net... 
    >
    > Thanks all -
    >
    > seems i should avoid using sysdate then and focus on to_date.
    >
    > thanks
    >
    > Tom
    >
    >[/ref]


    mcstock Guest

  9. #9

    Default Re: Difference between SYSDATE and TO_DATE

    Comment in-line:
    Tine Zorko <si> wrote in
    news:bnpcse$r21$arnes.si:
     

    This is total rubbish. The order of evaluation of the predicates is
    indeterminate. SQL is not a procedural language. With it you specify what
    your resultset should be, but the relational database determines how to
    get it, and that HOW can change from execution to execution.
    If you were right, the following could not happen:

    Create table p_order (
    SSN VARCHAR2(10),
    BIRTH_date DATE,
    ZIP VARCHAR2(10));

    insert into p_order values('8328486572', '31-OCT-03', '27722');
    insert into p_order values('8397333761', '31-OCT-03', 'A1180');
    insert into p_order values('8358002539', '31-OCT-03', '81225');
    insert into p_order values('8353962080', '31-OCT-03', '43933');

    SQL> select * from p_order;

    SSN BIRTH_DAT ZIP
    ---------- --------- ----------
    8328486572 31-OCT-03 27722
    8397333761 31-OCT-03 A1180
    8358002539 31-OCT-03 81225
    8353962080 31-OCT-03 43933

    4 rows selected.

    SQL> select * from p_order
    2 where ssn like '835%'
    3 and birth_date = '31-OCT-03';

    SSN BIRTH_DAT ZIP
    ---------- --------- ----------
    8358002539 31-OCT-03 81225
    8353962080 31-OCT-03 43933

    SQL> select * from p_order
    2 where ssn like '835%'
    3 and birth_date = '31-OCT-03'
    4 and zip = 43933;
    and zip = 43933
    *
    ERROR at line 4:
    ORA-01722: invalid number

    If Oracle did evaluate the predicates in the order specified, by the time
    it gets to evaluate zip = 43933 (which requires a to_number conversion)
    the zips ARE numbers and the conversion would succeed. However, it
    obviously evaluates "zip = " before "ssn like".

    Unless you specifically request it with the ORDERED_PREDICATES hint:

    SQL> select /*+ ORDERED_PREDICATES */ * from p_order
    2 where ssn like '835%'
    3 and birth_date = '31-OCT-03'
    4 and zip = 43933;

    SSN BIRTH_DAT ZIP
    ---------- --------- ----------
    8353962080 31-OCT-03 43933

    1 row selected.

    --
    What lies behind us and what lies before us are small matters when
    compared to what lies within us.

    Wolfgang Breitling
    Oracle 7, 8, 8i, 9i OCP
    Wolfgang Guest

  10. #10

    Default Re: Difference between SYSDATE and TO_DATE

    Wolfgang, you didn't read very carefully before you got on your soapbox

    Tine was illustrating how the BETWEEN operator works, not how multiple
    predicates are evaluated.

    This is the issue, in its simplest form:

    SQL> select *
    2 from dept
    3 where deptno between 30 and 10;

    no rows selected

    SQL> select *
    2 from dept
    3 where deptno between 10 and 30;

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING New York, NY
    20 RESEARCH DALLAS
    30 SALES CHICAGO

    "Wolfgang Breitling" <com> wrote in message
    news:161.157.145... 
    >
    > This is total rubbish. The order of evaluation of the predicates is
    > indeterminate. SQL is not a procedural language. With it you specify what
    > your resultset should be, but the relational database determines how to
    > get it, and that HOW can change from execution to execution.
    > If you were right, the following could not happen:
    >
    > Create table p_order (
    > SSN VARCHAR2(10),
    > BIRTH_date DATE,
    > ZIP VARCHAR2(10));
    >
    > insert into p_order values('8328486572', '31-OCT-03', '27722');
    > insert into p_order values('8397333761', '31-OCT-03', 'A1180');
    > insert into p_order values('8358002539', '31-OCT-03', '81225');
    > insert into p_order values('8353962080', '31-OCT-03', '43933');
    >
    > SQL> select * from p_order;
    >
    > SSN BIRTH_DAT ZIP
    > ---------- --------- ----------
    > 8328486572 31-OCT-03 27722
    > 8397333761 31-OCT-03 A1180
    > 8358002539 31-OCT-03 81225
    > 8353962080 31-OCT-03 43933
    >
    > 4 rows selected.
    >
    > SQL> select * from p_order
    > 2 where ssn like '835%'
    > 3 and birth_date = '31-OCT-03';
    >
    > SSN BIRTH_DAT ZIP
    > ---------- --------- ----------
    > 8358002539 31-OCT-03 81225
    > 8353962080 31-OCT-03 43933
    >
    > SQL> select * from p_order
    > 2 where ssn like '835%'
    > 3 and birth_date = '31-OCT-03'
    > 4 and zip = 43933;
    > and zip = 43933
    > *
    > ERROR at line 4:
    > ORA-01722: invalid number
    >
    > If Oracle did evaluate the predicates in the order specified, by the time
    > it gets to evaluate zip = 43933 (which requires a to_number conversion)
    > the zips ARE numbers and the conversion would succeed. However, it
    > obviously evaluates "zip = " before "ssn like".
    >
    > Unless you specifically request it with the ORDERED_PREDICATES hint:
    >
    > SQL> select /*+ ORDERED_PREDICATES */ * from p_order
    > 2 where ssn like '835%'
    > 3 and birth_date = '31-OCT-03'
    > 4 and zip = 43933;
    >
    > SSN BIRTH_DAT ZIP
    > ---------- --------- ----------
    > 8353962080 31-OCT-03 43933
    >
    > 1 row selected.
    >
    > --
    > What lies behind us and what lies before us are small matters when
    > compared to what lies within us.
    >
    > Wolfgang Breitling
    > Oracle 7, 8, 8i, 9i OCP[/ref]


    mcstock Guest

  11. #11

    Default Re: Difference between SYSDATE and TO_DATE

    "mcstock" <com> wrote in
    news:com:
     

    I am not so sure that I totally misunderstood Tine. The and in the
    between clause has absolutely nothing to do with the logical AND he (or
    she?) is explaining below. The and in between expresses a range, not a
    1st and 2nd argument and if the lower bound is greater than the upper
    bound the range is empty and the between clause evaluates to false. You
    can't just take part of it and say it is false and I therefore do not
    need to look at the rest.
     [/ref][/ref]

    Even that is false. between is replaced by the optimizer with

    " operand >= lower bound AND operand <= upper bound "

    which makes it obvious why combined they are false when the lower bound
    is greater than the upper bound.

    --
    What lies behind us and what lies before us are small matters when
    compared to what lies within us.

    Wolfgang Breitling
    Oracle 7, 8, 8i, 9i OCP
    Wolfgang Guest

Similar Threads

  1. Is there a difference???
    By evil-bear in forum Coldfusion Database Access
    Replies: 1
    Last Post: January 19th, 04:30 PM
  2. function NOW() to return sysdate
    By Neil in forum Oracle Server
    Replies: 3
    Last Post: October 29th, 03:12 PM
  3. Replies: 0
    Last Post: July 29th, 10:37 PM
  4. Replies: 0
    Last Post: July 29th, 05:26 AM
  5. Replies: 1
    Last Post: July 17th, 03:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139