CODEWITHSUNDEEP
sqldateleap-year
David
David

Reputation: 11

Oracle SQL - Same day last year (leap year proof)

I'm looking to find the same day last year in oracle sql. For example Wednesday, March 16, 2016 would be Wednesday, March 18, 2015 for last year. So the closest day.

The following code worked good until the current leap year and broke after Feb 29th 2016.

this was my old statement which does not work for all dates:

NEXT_DAY(TRUNC(ADD_MONTHS(date, -12), 'iw')-2, TO_CHAR(date, 'DY'))

Upvotes: 1

Views: 3664

Answers (3)

Adarsh Nanu
Adarsh Nanu

Reputation: 2211

How about add_months( [date], -12 ) ?

Upvotes: 0

Brian
Brian

Reputation: 13571

I think this may be correct

SQL Test:

WITH data
     AS (    SELECT SYSDATE - (LEVEL - 1) this_year_date,
                    TO_NUMBER (TO_CHAR (SYSDATE - (LEVEL - 1), 'D'))
                       this_year_day_of_week,
                    ADD_MONTHS (SYSDATE - (LEVEL - 1), -12) last_year_date,
                    TO_NUMBER (
                       TO_CHAR (ADD_MONTHS (SYSDATE - (LEVEL - 1), -12), 'D'))
                       last_year_day_of_week
               FROM DUAL
         CONNECT BY LEVEL <= 300),
     crunching
     AS (SELECT data.*,
                (CASE
                    WHEN this_year_day_of_week > last_year_day_of_week
                    THEN
                       this_year_day_of_week - last_year_day_of_week
                    WHEN this_year_day_of_week = last_year_day_of_week
                    THEN
                       0
                    ELSE
                       last_year_day_of_week - this_year_day_of_week
                 END)
                   math
           FROM data)
SELECT TO_CHAR (crunching.this_year_date, 'yyyy-MM-dd Day') ty_date,
       math,
       (CASE
           WHEN math = 0
           THEN
              TO_CHAR (last_year_date, 'yyyy-MM-dd Day')
           WHEN math > 2
           THEN
              TO_CHAR ( (last_year_date - math) + 7, 'yyyy-MM-dd Day')
           ELSE
              TO_CHAR (last_year_date + math, 'yyyy-MM-dd Day')
        END)
          final_answer
  FROM crunching

SQL Fiddle: http://sqlfiddle.com/#!4/9eecb7d/18211

Output:

TY_DATE MATH FINAL_ANSWER

2016-03-17 Thursday     2   2015-03-19 Thursday 
2016-03-16 Wednesday    2   2015-03-18 Wednesday
2016-03-15 Tuesday      2   2015-03-17 Tuesday  
2016-03-14 Monday       5   2015-03-16 Monday   
2016-03-13 Sunday       5   2015-03-15 Sunday   
2016-03-12 Saturday     2   2015-03-14 Saturday 
2016-03-11 Friday       2   2015-03-13 Friday   
2016-03-10 Thursday     2   2015-03-12 Thursday 
2016-03-09 Wednesday    2   2015-03-11 Wednesday
2016-03-08 Tuesday      2   2015-03-10 Tuesday  
2016-03-07 Monday       5   2015-03-09 Monday   
2016-03-06 Sunday       5   2015-03-08 Sunday   
2016-03-05 Saturday     2   2015-03-07 Saturday 
2016-03-04 Friday       2   2015-03-06 Friday   
2016-03-03 Thursday     2   2015-03-05 Thursday 
2016-03-02 Wednesday    2   2015-03-04 Wednesday
2016-03-01 Tuesday      2   2015-03-03 Tuesday  
2016-02-29 Monday       5   2015-03-02 Monday   
2016-02-28 Sunday       6   2015-03-01 Sunday   
2016-02-27 Saturday     1   2015-02-28 Saturday 
2016-02-26 Friday       1   2015-02-27 Friday   
2016-02-25 Thursday     1   2015-02-26 Thursday 
2016-02-24 Wednesday    1   2015-02-25 Wednesday
2016-02-23 Tuesday      1   2015-02-24 Tuesday  
2016-02-22 Monday       1   2015-02-23 Monday   
2016-02-21 Sunday       6   2015-02-22 Sunday   
2016-02-20 Saturday     1   2015-02-21 Saturday 
2016-02-19 Friday       1   2015-02-20 Friday   
2016-02-18 Thursday     1   2015-02-19 Thursday 
2016-02-17 Wednesday    1   2015-02-18 Wednesday

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

Simply subtract 7*52 = 364 days instead :-)

Upvotes: 0

Related Questions