在 Oracle 中使用 SYSDATE 编写 IF 语句时需要帮助

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/5752710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 23:27:04  来源:igfitidea点击:

Need Help in Writing IF Statement Using SYSDATE in Oracle

sqloracledate-arithmetic

提问by Sai Srikanth

I have an employee table. I Have to get the data of all employees in such a way that. If today I run the Query,then I have to get the data of all employees working between december 1st of previous year(current year-1 i.e., december 1st 2010.) till today(april 21st). If the query run date is in the month of december(example today is december 15th) then the query should get the data from december 1st of current year(december 1st 2011) to December 15th.

我有一张员工表。我必须以这种方式获取所有员工的数据。如果今天我运行查询,那么我必须获取在上一年 12 月 1 日(当前年份-1,即 2010 年 12 月 1 日)到今天(4 月 21 日)之间工作的所有员工的数据。如果查询运行日期在 12 月(例如今天是 12 月 15 日),那么查询应该获取从当年(2011 年 12 月 1 日)到 12 月 15 日的数据。

Can anyone help me writing down the IF or CASE Statement in this case. I wrote the if statement some how its not working. I want to make use of this If or Case Statement as the start date of the employee_timestamp. Is this possible here or not.

在这种情况下,谁能帮我写下 IF 或 CASE 声明。我写了 if 语句一些它是如何不起作用的。我想使用这个 If 或 Case Statement 作为 employee_timestamp 的开始日期。这是否可能在这里。

SELECT e.* 
  FROM EMPLOYEE e
 WHERE e.employee_timestamp > (SELECT TO_DATE(TO_CHAR(CONCAT('12-01-', EXTRACT(YEAR FROM SYSDATE)-1)),'MM/DD/YYYY') AS startdate 
                                 FROM DUAL)
   AND e.employee_timestamp < (SELECT SYSDATE FROM DUAL)

IF
((SELECT SYSDATE FROM DUAL)) > (select to_date(to_char(concat('11-30-', extract(YEAR FROM sysdate))),'MM/DD/YYYY') as Startdate From DUAL)
THEN (select to_date(to_char(concat('12-01-', extract(YEAR FROM sysdate))),'MM/DD/YYYY') as Startdate From DUAL)
ELSE
(select to_date(to_char(concat('12-01-', extract(YEAR FROM sysdate)-1)),'MM/DD/YYYY') as Startdate From DUAL)
END IF;

采纳答案by mulander

First some date manipulation examples without converting everything to and back from strings.

首先是一些日期操作示例,无需将所有内容从字符串转换为字符串。

SELECT SYSDATE - INTERVAL '1' YEAR -- returns the previous year from current date
 , TRUNC( SYSDATE, 'MM') -- returns the first day of the current month
 , ADD_MONTHS( TRUNC( SYSDATE, 'YYYY'), 11) -- returns December of current year
 , ADD_MONTHS( TRUNC( SYSDATE - INTERVAL '1' YEAR, 'YYYY'), 11) -- returns first day of december previous year
 , DECODE( TRUNC(SYSDATE, 'MM')
                                      , ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 11), TRUNC( SYSDATE, 'MM' )
                                      , ADD_MONTHS(TRUNC(SYSDATE, 'YYYY' ), 11) - INTERVAL '1' YEAR ) -- this implements your logic
  from dual

The last decode returns December 1st 2010 if the current month is NOT December 2011. If You run it on December 2011 it will return the 1st of December 2011.

如果当前月份不是 2011 年 12 月,则最后一次解码返回 2010 年 12 月 1 日。如果您在 2011 年 12 月运行它,它将返回 2011 年 12 月 1 日。

Now depending on how you want your date ranges you can use the following statements:

现在,根据您想要的日期范围,您可以使用以下语句:

If the date ranges are INCLUSIVE

如果日期范围是 INCLUSIVE

SELECT e.*
  FROM EMPLOYEE  e
 WHERE e.employee_timestamp  BETWEEN DECODE( TRUNC(SYSDATE, 'MM')
                                          , ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 11), TRUNC( SYSDATE, 'MM' )
                                          , ADD_MONTHS(TRUNC(SYSDATE, 'YYYY' ), 11) - INTERVAL '1' YEAR )
                            AND SYSDATE

If the date ranges are exactly like you specified (EXCLUSIVE)

如果日期范围与您指定的完全相同(独家)

   SELECT e.*
  FROM EMPLOYEE  e
 WHERE e.employee_timestamp  > DECODE( TRUNC(SYSDATE, 'MM')
                                          , ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 11), TRUNC( SYSDATE, 'MM' )
                                          , ADD_MONTHS(TRUNC(SYSDATE, 'YYYY' ), 11) - INTERVAL '1' YEAR )
   AND e.employee_timestamp  < SYSDATE

Hope this answers your question :) Remember to document this, code like this is hard to analyse years later.

希望这能回答您的问题 :) 记得记录下来,这样的代码在多年后很难分析。

回答by josephj1989

Select * from employee where 
employee_timestamp between
case
when to_char(sysdate,'mm') = '12'  then
                   trunc(sysdate,'MM')
else add_months(trunc(sysdate,'year'),-1)
end
and sysdate

回答by BadUncle

Without an if-statement this should get you the desired start-date to use in your query:

如果没有 if 语句,这应该可以让您在查询中使用所需的开始日期:

SELECT TRUNC( ADD_MONTHS( sysdate, MOD( TO_NUMBER( TO_CHAR(sysdate, 'MM') ), 12) * (-1) ), 'MONTH') 
FROM dual;

At least if I understood the requirements correctly...

至少如果我正确理解了要求......

回答by Andriy M

SELECT e.* 
  FROM EMPLOYEE e
WHERE e.employee_timestamp > ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 11) -
        INTERVAL CASE TO_CHAR(SYSDATE, 'MM') WHEN '12' THEN '0' ELSE '1' END YEAR
  AND e.employee_timestamp < SYSDATE

Seems like it should be rather e.employee_timestamp >= ...than e.employee_timestamp > ..., but you should know better, of course.

看起来它应该是而e.employee_timestamp >= ...不是e.employee_timestamp > ...,但你当然应该更清楚。