小于或等于 Oracle SQL
updated_date = 08-Jun-2010;
我有一个这样的查询
select * from asd whre updated_date <= todate('08-Jun-2010', 'dd-MM-yy');
但我没有得到任何结果.它仅适用于 todate 是 09-Jun-2010...
but I am not getting any results. it only works if todate is 09-Jun-2010...
即我的 equalto 操作符工作不正常.
i.e. my equalto operator is not working properly.
为什么会这样?
推荐答案
在 Oracle 中，DATE 是一个时间点.它总是有一个精确到秒的时间分量.todate('08-Jun-2010', 'dd-Mon-yyyy') 在 Oracle 中与 todate('08-Jun-2010 00:00:00', 'dd-Mon-yyyy hh24:mi:ss').因此，如果您选择截至该日期的行，您将不会在当天获得任何时间分量不等于 00:00 的行.
In Oracle a DATE is a point in time. It always has a time component with a precision to the second. todate('08-Jun-2010', 'dd-Mon-yyyy') is in Oracle the same as todate('08-Jun-2010 00:00:00', 'dd-Mon-yyyy hh24:mi:ss'). So if you select rows up to that date you won't get any row on that day with a time component not equal to 00:00.
如果您想选择直到并包括 08-JUN-2010 的所有行，我建议使用:
If you want to select all the rows up to and including 08-JUN-2010, I would suggest using:
< to_date('09-06-2010', 'dd-MM-yyyy')
或
<= to_date('08-06-2010 23:59:59', 'dd-MM-yyyy hh24:mi:ss')
注意 - 我更正了您的日期格式:如果您想使用缩写的月份名称，则需要使用 MON.我建议改用 MM，这样当有人更改其客户端设置 (NLS_DATE_LANGUAGE) 时，您就不会出错.也更喜欢使用 YYYY 而不是 YY.
Note - I corrected your date format: you need to use MON if you want to use the abbreviated month name. I would suggest using MM instead, so that you won't get error when someone changes its client settings (NLS_DATE_LANGUAGE). Also prefer the use of YYYY instead of YY.
相关文章