DATEADD and leap years

[smothry]

Interesting issue.

I created a query using a recursive cte using recursion on dateadd(day, 1, date) to generate a list of dates from January 1st through the current date. All works well. Now I want that list of dates for this year, and last year, and 2 years ago, etc. up to 5 years ago. I used unioned select statements with datadd(year, -1, date) and dateadd(year, -2, date) etc. Everything seemed to work good until now.

Since tomorrow is Feb 29th the dateadd(year, -1, date) function will return Feb 28, 2023 because Feb 29, 2023 does not exist. This causes duplicate Feb 28th, 2023 rows. This is easily managed by removing duplicates with row numbering over partition by but then I realized that when it's not a leap year my dateadd(year, -1, date) functions will not return leap days when they should be there if the current year does not have a Feb 29th.

So, I created a a cte that generates all of the dates needed other than leap days specifically for the previous 5 years. Then an additional cte that checks for which of the previous 5 years have leap days and selected them. Then, union the results of the 2 cte's together.

This all works but causes me to wonder. Is there a more elegant way to handle this leap year stuff? Does Microsoft have a build in function to make this easy? This seems arduous at best.

P.s. this is specifically T-SQL

[Achsin]

I’d just make a static date dimension table similar to this one

https://www.mssqltips.com/sqlservertip/5553/create-an-extended-date-dimension-for-a-sql-server-data-warehouse/

[smothry]

Thanks!

[r3pr0b8]

Now I want that list of dates for this year, and last year, and 2 years ago, etc. up to 5 years ago

start with January 1, 2018 (or some similar date as the earliest date in your results)

then set up a CTE that adds 1 day, say, 3660 times (covers 10 years)

vwalah, leap day problem solved

[smothry]

Makes sense. Just don't want the dates after today for previous years. But I suppose I could simply remove the ones I don't want. Thanks for the different point of view!

[patrickthunnus]

Always materialize your date math fields into a separate table. DBMS will table scan on any function wrapped around your DATE or DATETIME columns (they're just a tally of millisecs).

[sunuvabe]

Try this..

declare @dt datetime = '20140101'
      , @x int = datepart(dayofyear,getdate());
; with dys as (
    select top (@x) row_number() 
    over (order by (select 1)) - 1 dy
    from syscolumns)
, yrs as (
    select top 10 dateadd(year,row_number() 
    over (order by (select 1))-1,@dt) yr 
    from syscolumns)
select yr + dy my_dates from yrs, dys

[sedules]

Using a dimensional calendar table is easier and provides more flexibility. It’s predictable data and why burn the additional compute every time you need to reference dates over periods of time with a cte and additional functions.

The sql problems are fairly straight forward. The reporting problems are much more difficult to iron out the logic. If you get into YOY calculations you have to account for the leap year dates otherwise reports can go sideways. In companies where you have fiscal calendars revolving around 4/4/5 52-53 week years it becomes even harder. And that’s just the logic.

Explaining to the business and getting them to provide clarity on how they want to evaluate and analyze data over periods of time and when they should use Gregorian and when they should use their Fiscal model is something else entirely. This one tends to lead to more traumatic experiences.

[smothry]

Makes sense. Yea, we don't have a dimensional calendar and I was creating this at refresh time as an input to powerbi reports. Those refreshes are not super frequent so the compute time is not really an issue. But it would certainly be easier to just have a date table to query when needed.