Geeks With Blogs

The Wrecking Bawl Destructuring query language, one keyword at a time.
Want to create a SQL Server query using a table created at runtime that just has month numbers and year numbers?  I did, so here's how I did it:

DECLARE @StartDate datetime = '9/1/12', @EndDate datetime = '8/1/13'

CREATE TABLE #cal (TheYear int, TheMonth int)

SELECT YEAR(dateadd(month, number, @StartDate)), MONTH(dateadd(month, number, @StartDate))
FROM (SELECT DISTINCT number FROM master.dbo.spt_values WHERE name IS NULL) n
WHERE DATEADD(month, number, @StartDate) < @EndDate

You can use a table variable instead, quite easily, but SSRS 2008 doesn't allow them so I used a temp table.
Posted on Wednesday, August 7, 2013 11:19 AM all tech stuff , SQL Server | Back to top

Comments on this post: generating temporary table for left join with date range

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Alex Bransky | Powered by: