Easiest way to populate a temp table with dates between and including 2 date parameters

by

This works even if the @StartDate is not the first of the month. I’m assuming that if it’s not the start of the month, you want to begin with the first of the next month. Otherwise remove the +1.:

;WITH cte AS (
SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate 
            ELSE DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0) END AS myDate
UNION ALL
SELECT DATEADD(Month,1,myDate)
FROM cte
WHERE DATEADD(Month,1,myDate) <=  @EndDate
)
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)

More Related Contents:

  1. Get top 1 row of each group
  2. Referring to a Column Alias in a WHERE Clause
  3. Convert Month Number to Month Name Function in SQL
  4. SET versus SELECT when assigning variables?
  5. Generate a resultset of incrementing dates in TSQL
  6. Getting result of dynamic SQL into a variable for sql-server
  7. How to get difference between two rows for a column field?
  8. Better techniques for trimming leading zeros in SQL Server?
  9. How to print VARCHAR(MAX) using Print Statement?
  10. Convert Xml to Table SQL Server
  11. What’s the best way to select the minimum value from several columns?
  12. Concatenate values based on ID
  13. Delete duplicate records from a SQL table without a primary key
  14. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  15. Splitting delimited values in a SQL column into multiple rows
  16. varbinary to string on SQL Server
  17. How do I group on continuous ranges
  18. How can a LEFT OUTER JOIN return more records than exist in the left table?
  19. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  20. Column name or number of supplied values does not match table definition
  21. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  22. SQL Server 2005 ROW_NUMBER() without ORDER BY
  23. Can an INNER JOIN offer better performance than EXISTS
  24. How do you copy a record in a SQL table but swap out the unique id of the new row?
  25. Cumulative Total in MS Sql server [duplicate]
  26. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  27. Compare dates in T-SQL, ignoring the time part
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Distributed transaction error?
  30. Determine what user created objects in SQL Server

Leave a Comment