CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Sai Srikanth
Sai Srikanth

Reputation: 85

Need Help in Writing IF Statement Using SYSDATE in Oracle

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.

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.

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;

Upvotes: 2

Views: 7148

Answers (4)

Andriy M
Andriy M

Reputation: 77677

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.

Upvotes: 0

josephj1989
josephj1989

Reputation: 9709

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

Upvotes: 1

mulander
mulander

Reputation: 196

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.

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

If the date ranges are 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.

Upvotes: 2

BadUncle
BadUncle

Reputation: 11

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

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...

Upvotes: 1

Related Questions