CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
user1640480
user1640480

Reputation: 49

How to compare two dates in oracle11g

i am new to oracle. i need to compare date column with the current date in oracle 11g

for an example my table is

srno        dob
1       1992-04-01
2       1988-04-01  
3       1995-04-01

so i have to compre dob with the sysdate. if it is matched then must show the data.

i have tried this query to get result.

select dob 
from xyz 
where extract(month from dob)=extract(month from sysdate) 
  and extract(day from dob)=extract(day from sysdate);

but it is not working. please tell me where am i going wrong.

thanks.

Upvotes: 1

Views: 3625

Answers (6)

MontyPython
MontyPython

Reputation: 2994

There are easier ways to compare two dates in Oracle. Try the solution below:

select random_date_1, random_date_2,

-- when you have to match the complete date
/* use to_char(random_date_1,'Dd-Mon-Yy hh24.mi.ss') 
 when comparing date time */
/* use to_char(random_date_1,'Dd-Mon-Yy hh24')
 when only checking the date and hour (this is actually useful in a scenarios */

case when trunc(random_date_1) = trunc(random_date_2) 
then 'Match' else 'No Match' end as method_1,
case when to_char(random_date_1,'Dd-Mon-Yy') = to_char(random_date_2,'Dd-Mon-Yy') 
then 'Match' else 'No Match' end as method_2,

-- when you have to match only month
case when trunc(random_date_1,'Mon') = trunc(random_date_2,'Mon')
then 'Match' else 'No Match' end as method_3,
case when to_char(random_date_1,'Mon') = to_char(random_date_2,'Mon') 
then 'Match' else 'No Match' end as method_4

from
(select to_date(round (dbms_random.value (24, 31))
        || '-'
        || round (dbms_random.value (01, 01))
        || '-'
        || round (dbms_random.value (2015, 2015)),
        'DD-MM-YYYY') + level - 1 random_date_1, 
       to_date(round (dbms_random.value (27, 31))
        || '-'
        || round (dbms_random.value (01, 01))
        || '-'
        || round (dbms_random.value (2015, 2015)),
        'DD-MM-YYYY') + level - 1 random_date_2 from dual
connect by level <= 10);

Upvotes: 1

ngrashia
ngrashia

Reputation: 9894

What is the error you are getting? In your DB is dob field defined as DATE or varchar2?

In case your DB field is varchar2, then you may have to use,

SELECT * FROM XYZ
WHERE TRUNC ( TO_DATE(DOB, 'YYYY-MM-DD') ) = TRUNC (SYSDATE);

Upvotes: 0

Mike Meyers
Mike Meyers

Reputation: 2895

I've just tried out the example you gave in your question and can't see what is wrong with using extract, as you have shown:

select dob 
from xyz 
where extract(month from dob)=extract(month from sysdate) 
  and extract(day from dob)=extract(day from sysdate);

Or am I misunderstanding something? You say that the code isn't working but I can't see what you mean.

I prefer to use extract rather than to_char in this sort of situation as I feel that it more clearly represents what I want. I don't want a character representation of the date, I just want to compare the month and the day.

Here is a SQLFiddle with an example: http://sqlfiddle.com/#!4/c545c/2

Upvotes: 0

kayakpim
kayakpim

Reputation: 995

As said above use to_char if you need to do a conversion, and look at the different format masks available. If your dob datatype is a date then you can compare directly with the SYSDATE value. As above getting just MMDD will give you just the month (04) and the day (01) as a char string for comparison.

Upvotes: 0

Besher
Besher

Reputation: 570

Try this

SELECT DOB
FROM XYZ 
WHERE TRUNC (DOB) = TRUNC (SYSDATE)

Upvotes: 0

Rene
Rene

Reputation: 10541

select ...
where to_char(dob,'MMDD')=to_char(sysdate,'MMDD')

Upvotes: 1

Related Questions