# Thread: Trying to determine the same day of the previous year

1. Registered User
Join Date
Sep 2008
Posts
5

## Unanswered: Trying to determine the same day of the previous year

Hello,

I am trying to write a function that can determine the same day as today for the previous year. For example, today is 3rd day of the 39th week, what is the 3rd day of the 39th week of last year? Does anyone have any way of calculating this? So far here is what I have, assuming you have four cases, but case 4 doesn't work...

1. Current year is a leap year and last year is not
2. Current year is not a leap year and last year is (before 2/29)
3. Current year is not a leap year and last year is (after 2/29)
4. Current year is not a leap year and last year is not a leap year.

FYI - I am working with DB2

Code:
```create function f_sameday (indate date) returns date
return (
select case
when mod (year(indate),4) = 0
and mod (year(indate-1 year),4) != 0
then ((week(indate)-1)* 7+dayofweek(indate)-2) days+ date(cast(year(indate) -1 as char(4)) ||'-01-01')
when mod (year(indate),4) != 0
and mod (year(indate-1 year),4) = 0
and dayofyear(indate) <=60
then ((week(indate)-1)* 7+dayofweek(indate)) days+ date(cast(year(indate) -1 as char(4)) ||'-01-01')
when mod (year(indate),4) != 0
and mod (year(indate-1 year),4) = 0
and dayofyear(indate) > 60
then ((week(indate)-1)* 7+dayofweek(indate)+ 1) days+ date(cast(year(indate) -1 as char(4)) ||'-01-01')
when mod (year(indate),4) != 0
and mod (year(indate-1 year),4) != 0
then ((week(indate)-1)* 7+dayofweek(indate)) days+ date(cast(year(indate) -1 as char(4)) ||'-01-01')
end
from sysibm.sysdummy1
);```

2. Registered User
Join Date
Sep 2008
Posts
5
By the way - I think I am going down the wrong path - i am calculating the number of days when really I should be determining the week number and day of the current date and then finding that same week number and day and converting back to a date, which I don't know how to do.

3. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
week number is fraught with whoopsies

what database system is this? mysql?

because you surely will not want an ANSI SQL solution

4. Registered User
Join Date
Sep 2008
Posts
5
I am working in DB2, so you are right an ANSI solution might not work...

5. Registered User
Join Date
Sep 2008
Posts
5
I was able to simplify down to this:

Code:
```create function f_ParallelDate (indate date)
returns date
return (select (date(days(date(cast(year(indate)-1 as char(4))|| '-01-01')) -(dayofweek(date(cast(year(indate)-1 as char(4))|| '-01-01') )-1))+7 days)+(((week(indate)-2)* 7)+dayofweek(indate)-1) days
from sysibm.sysdummy1);```
any thoughts?

6. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
moving thread to db2 forum

7. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Provided Answers: 5
maybe I'm being too simplistic, but what about something like current date - 1 year? Also, instead of the select against sysdummy just use set my_date = current date - 1 year. Lastly, if I'm not being too simplistic, then why create a function at all?

8. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by dav1mo
maybe I'm being too simplistic, but what about something like current date - 1 year?
This would give the same date (i.e., day and month) in the previous year, not the same day-of-week; apparently, that's what yyb wants.

What about
Code:
`current date - 52 weeks`
? This will of course skip weeks once in about 7 years, but that should be easy to compensate for by either using the week() function, or by finding out for which years this jump occurs (through a formula) and subtracting 53 weeks in that case.

9. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Provided Answers: 5
You are correct, though -52 weeks does not work on z/os anyway. What could be done is (indate - 1 year + 1 day) with a case statement that determines if it the prior year or current year was/is a leap year then you would need to do (indate - 1 year + 2 days) for some dates of those years

10. Registered User
Join Date
Sep 2008
Posts
5
That seems like it would work, but now that i have fiddled with it, the function above does seem to work... can anyone see anything wrong with it?

11. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by dav1mo
-52 weeks does not work on z/os anyway.
Well, make that "- 364 DAYS". That works perfectly on z/OS.
This is what I get from the query
Code:
```WITH A(D) AS
(SELECT date('27.05.2008') FROM sysibm.sysdummy1
UNION ALL
SELECT date('27.05.2007') FROM sysibm.sysdummy1
UNION ALL
SELECT date('27.05.2006') FROM sysibm.sysdummy1
UNION ALL
SELECT date('27.05.2005') FROM sysibm.sysdummy1
UNION ALL
SELECT date('27.05.2004') FROM sysibm.sysdummy1
UNION ALL
SELECT date('27.05.2003') FROM sysibm.sysdummy1
UNION ALL
SELECT date('27.05.2002') FROM sysibm.sysdummy1
UNION ALL
SELECT date('27.05.2001') FROM sysibm.sysdummy1)
SELECT d - 364 days, week_iso(d), week_iso(d - 364 days) from a```
(sorry for using sysibm.sysdummy1 -- still on v8 ...)
Code:
```29.05.2007           22           22
28.05.2006           21           21
28.05.2005           21           21
28.05.2004           21           22
29.05.2003           22           22
28.05.2002           22           22
28.05.2001           22           22
28.05.2000           21           21```
So, by looking at this, I would do something like[CODE]d - 364 DAYS - (CASE MOD(year(d),7)
WHEN 3 THEN 7 ELSE 0 END) DAYS[CODE]which corrects the 2005 -> 2004 problem.
Unfortunately, it doen't work for 1999->1998 and 1998->1997 (and for 2010->2009 and 2012->2011 and ...).
We'll need a more clever algorithm...

12. Registered User
Join Date
Oct 2006
Location
CA
Posts
210
Old thread, I know. My Google search found it so here's my 2 cents.

Truth: 52 x 7 = 364.
Therefore; same-day-last-year is always 364 days ago.

It's up to the dateadd algorithm to maintain that truth across leap-year boundaries.

Ergo; this should work for all dates if you only want a 1 or 2 year look-back:

Code:
`Select @SameDayPriorYear = dateadd(d,-364,@myDate)`
My case: I'm writing a year-over-year Sales Report where the GM wants to see the sales booked for the same day last year. He doesn't care about comparisons with 2 or more years ago. In this economy, anything more than a year or two old is not very relevant.

Example of a good day in sales:
Current year: Monday 3/1/2010 \$210,000 total sales
Prior Year: Monday 3/2/2009 \$195,000 total sales for the same Monday last year.

.
.
.
... same thing Peter.V said but simplified for those with simple needs.
Last edited by vich; 10-03-13 at 19:32. Reason: add Peter comment

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•