在Oracle SQL中少于或等于 [英] LESS THAN OR EQUAL TO IN Oracle SQL

问题描述

updated_date  = 08-Jun-2010;

我有这样的查询

select * from asd whre updated_date <= todate('08-Jun-2010', 'dd-MM-yy');

但是我没有得到任何结果.只有todate是2010年6月9日...

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是一个时间点.它始终具有精确到秒的时间分量.在Oracle中，todate('08-Jun-2010', 'dd-Mon-yyyy')与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.

这篇关于在Oracle SQL中少于或等于的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！