compare dates and decode
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: compare dates and decode

  1. #1
    Join Date
    Nov 2000
    Posts
    173

    compare dates and decode

    Hi,

    While writing some pl/sql I have noticed my dates don't compare the same in the where clause vs. using decode. Executing from sqlplus.

    Simple select on date coumn.
    select rslt_dt from table;
    RSLT_DT
    ---------
    01-JAN-01

    In the where clause I have to compare the date like this:
    AND rd.rslt_dt <> '01-Jan-0001'

    In the decode, I have to use this one:
    DECODE(rd.rslt_dt, '01-JAN-01', ri.actl_dt, rd.rslt_dt) as rslt_dt,

    What gives here. How can this be different?

    Thanks,
    Kathy

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    convert them to dates, you are comparing strings

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    select rslt_dt from table;
    RSLT_DT
    ---------
    01-JAN-01 --- Is the year 0001?

  4. #4
    Join Date
    Apr 2007
    Posts
    2
    How can you campare two opposite conditions,means once you are cheking ing rd.rslt_dt= '01-JAN-01' then ri.actl_dt should come else rd.rslt_dt
    & in where clause how can you write rd.rslt_dt <> '01-Jan-0001'.
    If it will not equal to that date then how will decode work????

  5. #5
    Join Date
    Mar 2006
    Posts
    74
    Quote Originally Posted by kburrows
    In the where clause I have to compare the date like this:
    AND rd.rslt_dt <> '01-Jan-0001'
    You need to get really, really solid on the difference between a date and a string, or any data type..

    1 is a number
    '1' is a string containing a numerical character

    if you sort these ascending:
    1
    2
    10

    you get:
    1
    2
    10


    if you sort these ascending:
    '1'
    '2'
    '10'

    you get:
    '1'
    '10'
    '2'


    Are you starting to dig the difference between a number and a string?

    Now, a date is stored as a number of days and fractions of a day since a certain time. This is how we can do date math, e.g. this is 7 days ago:

    SYSDATE - 7


    if the surrent sysdate is 123456, meaning one hundred twenty three thousand, four hundred fifty six days AFTER the epoch (say its jan 01, 1901) then taking 7 days off gives us 123449, 1 week ago.

    A date of 0 is the epoch
    a date of 0.5 is half a day, or 12 hours after the epoch


    So you have a column stored as a date, and you say this:

    rd.rslt_dt <> '01-Jan-0001'

    WHAT IS IT DOING? Hmm..

    It might convert the date into a string in the default format as set in the options, and perform this:

    '01-JAN-1901' <> '01-Jan-0001'

    It might try and convert the string into a date:

    [a date of first january 1901] <> [a date of first january year 1 ad]


    But we just dont know! This is where stuff starts to go wrong.. leave a computer to guess? No! They are like children, they need telling explicitly otherwise something gets broken

    -

    Now, given the choice of converting a million dates to compare them to a constant string, or converting one constant string to a date to compare it with a million dates, what are we going to do? Too right we are going to avoid 999,999 pointless conversions that will be slow (its slower to compare strings than numbers)


    so we convert our string to a date:


    rd.rslt_dt <> TO_DATE('01-Jan-0001', 'DD-Mon-YYYY')


    hope this info is a useful starting tutorial. Never ever ever ever rely on implicit conversion of data types!!

  6. #6
    Join Date
    Mar 2006
    Posts
    74
    rd.rslt_dt <> '01-Jan-0001'


    DECODE(rd.rslt_dt, '01-JAN-01', ri.actl_dt, rd.rslt_dt) as rslt_dt,
    PS; if you cant tell the difference between the following two STRINGS:

    '01-Jan-0001'
    '01-JAN-01'

    then (to be blunt) you really need to read a little more before attempting to write SQLs. To a programmer, those strings are about the same as 'chalk' and 'cheese' - they are different lengths and the only thing they have in common are starting with the same few characters.

    Computers are precise creatures, they demand precise thinking..

Posting Permissions

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


Click Here to Expand Forum to Full Width