Oracle SQL比较日期

问题描述

SQL> select * from porder ;

   OID       BILL ODATE
  ------ ---------- ---------
    10        200 06-OCT-13
     4      39878 05-OCT-13
     5     430000 05-OCT-13
    11        427 06-OCT-13
    12        700 06-OCT-13
    14      11000 06-OCT-13
    15      35608 06-OCT-13
    13      14985 06-OCT-13
     8      33000 06-OCT-13
     9        600 06-OCT-13
    16        200 06-OCT-13

   OID       BILL ODATE
------ ---------- ---------
     1        200 04-OCT-13
     2      35490 04-OCT-13
     3       1060 04-OCT-13
     6        595 05-OCT-13
     7        799 05-OCT-13

16 rows selected.

 SQL> select * from porder where odate='04-Oct-2013';

no rows selected

请某人帮助...为什么查询不起作用.. ??

please someone help...why isn't the query working..??

推荐答案

在Oracle中，DATE始终具有时间成分.您的客户端可能显示也可能不显示时间分量，但是当您尝试进行相等比较时，它仍然存在.您还总是希望将日期与日期而不是字符串进行比较，后者使用当前会话的NLS_DATE_FORMAT进行隐式转换，从而使它们相当脆弱.这些将涉及ANSI日期文字或显式的to_date调用

In Oracle, a DATE always has a time component. Your client may or may not display the time component, but it is still there when you try to do an equality comparison. You also always want to compare dates with dates rather than strings which use the current session's NLS_DATE_FORMAT for doing implicit conversions thus making them rather fragile. THat will involve either ANSI date literals or explicit to_date calls

您可以使用TRUNC函数将DATE截断为午夜

You can use the TRUNC function to truncate the DATE to midnight

SELECT *
  FROM porder
 WHERE trunc(odate) = date '2013-10-04'

或者您可以进行范围比较(如果可以受益于odate上的索引，这将更加高效)

Or you can do a range comparison (which will be more efficient if you can benefit from an index on odate)

SELECT *
  FROM porder
 WHERE odate >= to_date( '04-Oct-2013', 'DD-Mon-YYYY' )
   AND odate <  to_date( '05-Oct-2013', 'DD-Mon-YYYY' );

这篇关于Oracle SQL比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！