Recently a colleague asked me if I could create a single SQL select to produce a list of every date within the current year and the last two years with a couple conditions;
1. No date transformation table would be available
2. The dates need to be generated on the fly
3. No while loops or cursors
Although the use case didn't merit this type solution, I took this as a challenge and worked to find a solution for this. My thought process, I just need to determine the last date of the current year and count back from there.
To get there; I cast the current year as varchar and pre-pended 12-31- to the year which would make the end date dynamic. Then cast the varchar back into a date format.
My next thought, I need a running number count of 0 - 1095.
(365 x 3) + 1 - This being days in a year x 3 years plus 1 in case of a leap year.
But where could would I find a list of numbers? Then I remembered the Master.dbo.SPT_Values table. This is an internal undocumented MS SQL table which is used as a helper table. The good news is you can query this table and filter by type = 'p' to get a sequential number list. It's bad practice to use this table for general processing, but for the purposes of this challenge it would work!
I now need 1095 individual dates so I filtered where number < =1095.
Next I performed a dateadd using the number field to get the full current year and the last two year. and to be sure I only get 3 years of data, I include the year in the where clause.
This was a fun challenge to work through. If you would have taken a different approach, I would love to hear about it. Leave a comment below with how you would have solved this challenge
Thanks for checking out this blog post. If you found this post helpful please consider donating. Any contribution is appreciated! Just click the PayPal icon at the bottom of this page.