Menu
What is the easiest way to populate a temp table with dates including and between 2 date parameters. I only need the 1st day of the month dates.
So for example if @StartDate = '2011-01-01' and @EndDate = '2011-08-01'
Then I want this returned in the table
2011-01-01 2011-02-01 2011-03-01 2011-04-01 2011-05-01 2011-06-01 2011-07-01 2011-08-01 
235
SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2001-02-01 10:00:00' AND '2007-03-01 22:00:00';
The SQL BETWEEN Operator The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
You can use an auxiliary table of numbers (Itzik's VATN is a nice one), a recursive cte, etc, and of course a calendar table as suggested by Arbi. Thanks v. vt, that did the trick. Please learn to use the ISO-8601 date format.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
