Get the same day of the same week last year
I am looking for a way to get the same day last year on a specific date.
I have a table with a date and want the second column on the same day but last year on the same day.
Example: Date: Monday 01 June 2009 Last year Date: Monday 02 June 2008
So, it should be the same day of the same week, but 1 year ago.
Is there a way to do this?
Thanks for the help?
source to share
Assuming "week" means week as defined by ISO-8601, you can use a function that converts the week number to a date.
CREATE FUNCTION IsoWeekDate(iso_year IN INTEGER, iso_week IN INTEGER) RETURN DATE AS
BEGIN
RETURN TRUNC(TO_DATE(iso_year || '0104', 'YYYYMMDD'), 'IW') + 7 * (iso_week - 1);
END IsoWeekDate;
Then you can do it like this:
SELECT
NEXT_DAY(IsoWeekDate(TO_CHAR(DATE '2009-06-01', 'IYYY')-1, TO_CHAR(DATE '2009-06-01', 'IW'))-1, TO_CHAR(DATE '2009-06-01', 'fmDAY'))
FROM YOUR_TABLE
You can of course write it all in one in SQL, I split it just for better visibility.
Note. For dates between December 29 and January 3, you might get unexpected results using this approach, since a year can be 52 or 53 weeks long, and those dates can be prior dates, respectively. next year.
source to share
Oracle 11g R2 schema setup :
CREATE FUNCTION TO_ISO_WEEK_DATE(
week NUMBER,
year NUMBER
) RETURN DATE DETERMINISTIC
IS
BEGIN
RETURN NEXT_DAY(
TO_DATE( TO_CHAR( year, '0000' ) || '0104', 'YYYYMMDD' )
- INTERVAL '7' DAY, 'MONDAY'
)
+ ( week - 1 ) * 7;
END TO_ISO_WEEK_DATE;
/
CREATE FUNCTION SAME_DAY_AND_WEEK_OF_YEAR(
dt DATE,
year NUMBER
) RETURN DATE DETERMINISTIC
IS
p_week NUMBER(2,0) := TO_NUMBER( TO_CHAR( dt, 'IW' ) );
p_day VARCHAR2(9) := TO_CHAR( dt, 'DAY' );
BEGIN
RETURN NEXT_DAY( TO_ISO_WEEK_DATE( p_week, year ) - 1, p_day );
END SAME_DAY_AND_WEEK_OF_YEAR;
/
Request 1 :
SELECT TO_CHAR( SYSDATE + LEVEL - 1, 'YYYY-MM-DD (IW-D)' ) AS "Date 2015",
TO_CHAR( SAME_DAY_AND_WEEK_OF_YEAR( SYSDATE + LEVEL - 1, 2014 ), 'YYYY-MM-DD (IW-D)' ) AS "Date 2014",
TO_CHAR( SAME_DAY_AND_WEEK_OF_YEAR( SYSDATE + LEVEL - 1, 2013 ), 'YYYY-MM-DD (IW-D)' ) AS "Date 2013",
TO_CHAR( SAME_DAY_AND_WEEK_OF_YEAR( SYSDATE + LEVEL - 1, 2012 ), 'YYYY-MM-DD (IW-D)' ) AS "Date 2012",
TO_CHAR( SAME_DAY_AND_WEEK_OF_YEAR( SYSDATE + LEVEL - 1, 2011 ), 'YYYY-MM-DD (IW-D)' ) AS "Date 2011"
FROM DUAL
CONNECT BY LEVEL <= 7
Results :
| Date 2015 | Date 2014 | Date 2013 | Date 2012 | Date 2011 |
|-------------------|-------------------|-------------------|-------------------|-------------------|
| 2015-06-12 (24-6) | 2014-06-13 (24-6) | 2013-06-14 (24-6) | 2012-06-15 (24-6) | 2011-06-17 (24-6) |
| 2015-06-13 (24-7) | 2014-06-14 (24-7) | 2013-06-15 (24-7) | 2012-06-16 (24-7) | 2011-06-18 (24-7) |
| 2015-06-14 (24-1) | 2014-06-15 (24-1) | 2013-06-16 (24-1) | 2012-06-17 (24-1) | 2011-06-19 (24-1) |
| 2015-06-15 (25-2) | 2014-06-16 (25-2) | 2013-06-17 (25-2) | 2012-06-18 (25-2) | 2011-06-20 (25-2) |
| 2015-06-16 (25-3) | 2014-06-17 (25-3) | 2013-06-18 (25-3) | 2012-06-19 (25-3) | 2011-06-21 (25-3) |
| 2015-06-17 (25-4) | 2014-06-18 (25-4) | 2013-06-19 (25-4) | 2012-06-20 (25-4) | 2011-06-22 (25-4) |
| 2015-06-18 (25-5) | 2014-06-19 (25-5) | 2013-06-20 (25-5) | 2012-06-21 (25-5) | 2011-06-23 (25-5) |
source to share
Just add 364 whether it is a leap year or not, for example
select
to_char(to_date('24/01/2016','dd/mm/yyyy'),'DAY DD/MM/YYYY') as CrossesLeapYear_PivotDate,
to_char(add_months(to_date('24/01/2016','dd/mm/yyyy'),12), 'DAY DD/MM/YYYY') as CrossesLeapYear_NextYearDate,
to_char(to_date('24/01/2016','dd/mm/yyyy') + 364, 'DAY DD/MM/YYYY') as CrossesLeapYear_SameNextYear,
to_char(to_date('24/01/2017','dd/mm/yyyy'),'DAY DD/MM/YYYY') as NoLeapYear_PivotDate,
to_char(add_months(to_date('24/01/2017','dd/mm/yyyy'),12), 'DAY DD/MM/YYYY') as NoLeapYear_NextYearDate,
to_char(to_date('24/01/2017','dd/mm/yyyy') + 364, 'DAY DD/MM/YYYY') as NoLeapYear_SameNextYear
from dual
source to share