Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Easiest way to populate a temp table with dates between and including 2 date parameters
- 2011-01-01
- 2011-02-01
- 2011-03-01
- 2011-04-01
- 2011-05-01
- 2011-06-01
- 2011-07-01
- 2011-08-01
- ;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)
- declare @StartDate datetime
- declare @EndDate datetime
- select @StartDate = '2011-01-01' , @EndDate = '2011-08-01'
- select @StartDate= @StartDate-(DATEPART(DD,@StartDate)-1)
- declare @temp table
- (
- TheDate datetime
- )
- while (@StartDate<=@EndDate)
- begin
- insert into @temp
- values (@StartDate )
- select @StartDate=DATEADD(MM,1,@StartDate)
- end
- select * from @temp
- DECLARE @StartDate DATETIME
- ,@EndDate DATETIME;
- SELECT @StartDate = '20110105'
- ,@EndDate = '20110815';
- SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, v.number, @StartDate)), 0) AS FirstDay
- --or Andriy M suggestion:
- --SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) + v.number, 0) AS FirstDay
- INTO #Results
- FROM master.dbo.spt_values v
- WHERE v.type = 'P'
- AND DATEDIFF(MONTH, @StartDate, @EndDate) >= v.number;
- SELECT *
- FROM #Results;
- DROP TABLE #Results;
- FirstDay
- -----------------------
- 2011-01-01 00:00:00.000
- 2011-02-01 00:00:00.000
- 2011-03-01 00:00:00.000
- 2011-04-01 00:00:00.000
- 2011-05-01 00:00:00.000
- 2011-06-01 00:00:00.000
- 2011-07-01 00:00:00.000
- 2011-08-01 00:00:00.000
Add Comment
Please, Sign In to add comment