选择某一年 Oracle 的记录 [英] Select records for a certain year Oracle
问题描述
我有一个存储交易和日期列的 oracle 表.如果我需要选择一年的记录,比如 2013 年,我这样做:
I have an oracle table that store transaction and a date column. If I need to select records for one year say 2013 I do Like this:
select *
from sales_table
where tran_date >= '01-JAN-2013'
and tran_date <= '31-DEC-2013'
但我需要一种直接的方式来选择一年的记录,比如从应用程序中传递参数2013",以从那一年的记录中获取结果而不给出范围.这可能吗?
But I need a Straight-forward way of selecting records for one year say pass the Parameter '2013' from an Application to get results from records in that one year without giving a range. Is this Possible?
推荐答案
你可以使用to_date函数
http://psoug.org/reference/date_func.html
select *
from sales_table
where tran_date >= to_date('1.1.' || 2013, 'DD.MM.YYYY') and
tran_date < to_date('1.1.' || (2013 + 1), 'DD.MM.YYYY')
具有显式比较的解决方案 (tran_date >= ... and tran_date < ...)
能够在 tran_date 上使用索引
字段.
solution with explicit comparisons (tran_date >= ... and tran_date < ...)
is able to use index(es) on tran_date
field.
考虑边界:例如如果 tran_date = '31.12.2013 18:24:45.155'
则您的代码 tran_date <='31-DEC-2013'
将 miss它
Think on borders: e.g. if tran_date = '31.12.2013 18:24:45.155'
than your code tran_date <='31-DEC-2013'
will miss it
这篇关于选择某一年 Oracle 的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!