无法在oracle中正确比较2个日期 [英] Can't compare 2 dates in oracle correctly

问题描述

我有这个sql查询，它检索记录直到 12/7/2016 ，问题是，oracle返回的记录是2016年12月6日及之后的记录.请问这是正确的行为吗?

I have this sql query which retrieve records until 12/7/2016 the problem is, oracle returns records which is from 12/6/2016 and below. May I ask is this the correct behavior?

SELECT DISTINCT ACCOUNT_NO
FROM TBL_CLIENT_FINANCIAL_ACTIVITY cfa
WHERE CAST(tran_date as DATE) <= TO_DATE('12/07/2016');

推荐答案

我假设您正在使用MM/DD/YYYY?在您的to_date()中声明它.

I am assuming you are using MM/DD/YYYY? Declare it in your to_date().

to_date('12/07/2016','MM/DD/YYYY')

此外，让我们删节该新日期以与您声明的日期相匹配，否则剩余的小时数分钟秒数要晚于您的to_date值.

Also, let's trunc that new date to match the date you have stated, otherwise those leftover hours minutes seconds are later than your to_date value.

trunc(CAST(tran_date as DATE)) <= to_date('12/07/2016','MM/DD/YYYY')

这篇关于无法在oracle中正确比较2个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！