Trying to determine the same day of the previous year

yyb · #1 (**permalink**) 09-23-08, 14:26

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
);

yyb · #2 (**permalink**) 09-23-08, 14:31

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.

r937 · #3 (**permalink**) 09-23-08, 16:31

week number is fraught with whoopsies

what database system is this? mysql?

because you surely will not want an ANSI SQL solution

yyb · #4 (**permalink**) 09-23-08, 16:37

I am working in DB2, so you are right an ANSI solution might not work...

yyb · #5 (**permalink**) 09-23-08, 16:47

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?

r937 · #6 (**permalink**) 09-23-08, 16:56

moving thread to db2 forum

dav1mo · #7 (**permalink**) 09-23-08, 17:37

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?

Peter.Vanroose · #8 (**permalink**) 09-24-08, 02:39

Quote:

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.

dav1mo · #9 (**permalink**) 09-24-08, 15:06

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

yyb · #10 (**permalink**) 09-24-08, 15:10

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?

Peter.Vanroose · #11 (**permalink**) 09-24-08, 17:05

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech