在Oracle中检索日期范围内的数据 [英] Retrieve data within a date range in Oracle

问题描述

我有一个tbldeptdivision表，如下所示:

I have a table tbldeptdivision as follows:

ID  DEPTID  DIVISIONID  FROMDATE    TODATE      REMARKS 
--- ------- ----------- ----------- ----------- --------
21  21      5           31-AUG-99   01-JAN-80   NULL

我有查询

select *
  from tbldeptdivision
 where deptid = 21
   and trunc(sysdate) between to_date(fromdate,'dd-Mon-yyyy')
                          and to_date(todate,'dd-mon-yyyy');

它没有任何价值.有人可以说为什么吗? '31-AUG-99'实际上是'31-AUG-1999'，而'01-JAN-80'实际上是'01-JAN-2080'.确切的查询是什么?

It returns me no value. Can anybody say why? '31-AUG-99' is actually '31-AUG-1999' and '01-JAN-80' is actually '01-JAN-2080'. What will be the exact query?

推荐答案

由于您的todate是date，因此您的问题源于列值从日期到varchar再到日期的无用转换:

As your todate is a date your problem stems from the useless conversion of the column's value from a date to a varchar and back to a date:

to_date()将 VARCHAR 转换为DATE值.如果您传递给该函数的值是 ，则DATE Oracle首先会通过应用默认的NLS格式将您的日期隐式转换为varchar，然后将 that varchar转换为返回日期，再次应用默认的NLS格式.

to_date() converts a VARCHAR to a DATE value. If the value you pass to that function is already a DATE Oracle will first implicitely convert your date to a varchar by applying the default NLS format and will then convert that varchar back to a date, again applying the default NLS format.

在第一次(隐式)转换中，您正在失去当年的世纪，因此，当将varchar转换回date

In the first (implicit) conversion you are losing the century in your year, which consequently is then wrong when the varchar is converted back to a date

因此，根据您的情况，由于调用to_date(fromdate,'dd-Mon-yyyy')

So in your case the following is done due to the call to_date(fromdate,'dd-Mon-yyyy')

• todate包含(实际)日期值:1980-01-30
• 隐式转换为varchar使得'01-JAN-80'
• 从varchar转换为日期，然后假设80年应为2080(再次基于隐式数据类型转换的规则).

• todate contains the (real) date value: 1980-01-30
• the implicit conversion to a varchar makes that '01-JAN-80'
• the conversion from the varchar to a date then assumes the year 80 should be 2080 (again based on the rules for implicit data type conversion).

一般规则是:

如果您需要摆脱DATE列中的时间部分，请使用trunc()代替:

If you need to get rid of the time part in the DATE column use trunc() instead:

where trunc(sysdate) between trunc(fromdate) and trunc(todate)

这篇关于在Oracle中检索日期范围内的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！