使用Oracle BETWEEN函数限定查询结果的时间范围（LimitdatarangeinOracle）的调试经验分享

需求：

要创建一个半年生成一次的报表给领导看。现有的做法是在中提取出数据后在excel中通过filter筛选出最近半年的数据，没错，纯手动操作(弊端有二：容易出错，效率低)。如何自动化呢?使用vba有点小题大做，通过sql脚本实现即可。该between条件语句出场了。

代码：

and utable.udatefield between to_date('2017-12-01','yyyy-mm-dd') between to_date('2018-05-31','yyyy-mm-dd')

一枪头搞定，哈哈。

顺便看看oracle database sql language reference中对between condition的描述：

----------------------------------------------------------------------------------------------------------------

between condition

a between condition determines whether the value of one expression is in an interval

defined by two other expressions.

all three expressions must be numeric, character, or datetime expressions. in sql, it is

possible that expr1 will be evaluated more than once. if the between expression

appears in pl/sql, expr1 is guaranteed to be evaluated only once. if the expressions

are not all the same data type, then oracle database implicitly converts the

expressions to a common data type. if it cannot do so, then it returns an error.

the value of

expr1 not between expr2 and expr3

is the value of the expression

not (expr1 between expr2 and expr3)

and the value of

expr1 between expr2 and expr3

is the value of the boolean expression:

expr2 <= expr1 and expr1 <= expr3

if expr3 < expr2, then the interval is empty. if expr1 is null, then the result is null. if

expr1 is not null, then the value is false in the ordinary case and true when the

keyword not is used.

the boolean operator and may produce unexpected results. specifically, in the

expression x and y, the condition x is null is not sufficient to determine the value of

the expression. the second operand still must be evaluated. the result is false if the

second operand has the value false and null otherwise. see "logical conditions" on

page 7-8 for more information on and.