Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Question 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. #2
    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. #3
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  5. #5
    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. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to db2 forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    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. #10
    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. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    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 20: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
  •