compare dates and decode

[kburrows]

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

[davey23uk]

convert them to dates, you are comparing strings

[tamilselvan]

select rslt_dt from table;
RSLT_DT
---------
01-JAN-01 --- Is the year 0001?

[memory]

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

[cjard]

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

[cjard]

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