如何在oracle11g中比较两个日期

Question

我是甲骨文新手。我需要将日期列与oracle 11g中的当前日期进行比较。

例如，我的表是

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

所以我不得不用sysdate来压缩道布。如果匹配，则必须显示数据。

我已经尝试了这个查询来获得结果。

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

但它不起作用。请告诉我我哪里错了。

谢谢。

Answer 1

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

Answer 2

在Oracle中有更简单的方法来比较两个日期。尝试下面的解决方案：

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);

Answer 3

尝尝这个

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