用于附加月份和年份的预言机日期函数

Question

嗨，现在有这样的查询，我想在查询中附加值，以便tran_date数据库中采用日期时间格式：所以考虑这两个查询

查询 1：：我必须以日期格式附加值，但它不起作用..如何在日期类型中传递年份和月份：

select TRAN_DATE,
       trunc(nvl(SUM(SALES_VALUE), 0) + nvl(sum(total_sales), 0) -
             nvl(sum(net_sales), 0) + nvl(sum(discount), 0)) Sale
  from OUTLET_PAYMODE_REPORT_FACT A, OUTLET_DETAILS B
 WHERE A.OUTLET_ID = B.OUTLET_ID
   and SALES_VALUE > 0
   and to_date(TRAN_DATE, 'yyyy') = '2013'
   AND to_date(TRAN_DATE, 'MON') = 'APR'
    OR to_date(TRAN_DATE, 'yyyy') = '2013'
   AND to_date(TRAN_DATE, 'MON') = 'MAR'
 GROUP BY TRAN_DATE

查询 2：它的工作，但它是字符串格式

select TRAN_DATE,
       trunc(nvl(SUM(SALES_VALUE), 0) + nvl(sum(total_sales), 0) -
             nvl(sum(net_sales), 0) + nvl(sum(discount), 0)) Sale
  from OUTLET_PAYMODE_REPORT_FACT A, OUTLET_DETAILS B
 WHERE A.OUTLET_ID = B.OUTLET_ID
   and SALES_VALUE > 0
   and (to_char(TRAN_DATE, 'yyyy') = '2013' AND
       to_char(TRAN_DATE, 'MON') = 'APR')
    OR (to_char(TRAN_DATE, 'yyyy') = '2013' AND
       to_char(TRAN_DATE, 'MON') = 'MAR')
 GROUP BY TRAN_DATE

Answer 1

其中一个选项是将BETWEEN条件与DATETIME数据类型列一起使用：

select TRAN_DATE,
       trunc(nvl(SUM(SALES_VALUE), 0) + nvl(sum(total_sales), 0) -
             nvl(sum(net_sales), 0) + nvl(sum(discount), 0)) Sale
  from OUTLET_PAYMODE_REPORT_FACT A, OUTLET_DETAILS B
 WHERE A.OUTLET_ID = B.OUTLET_ID
   and SALES_VALUE > 0
   and TRAN_DATE between to_date('01.03.2013', 'dd.mm.yyyy') and
                         to_date('30.04.2013', 'dd.mm.yyyy')
 GROUP BY TRAN_DATE;