Select all dates between first day of month and current date

sql, sql-server-2008

Solution

Try this

DECLARE @startDate DATE=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR)  -- mm/dd/yyyy
DECLARE @endDate DATE=GETDATE() -- mm/dd/yyyy

SELECT [Date] = DATEADD(Day,Number,@startDate) 
FROM  master..spt_values 
WHERE Type='P'
AND DATEADD(day,Number,@startDate) <= @endDate

OR

DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATETIME= GETDATE() -- mm/dd/yyyy

;WITH Calender AS 
(
    SELECT @startDate AS CalanderDate
    UNION ALL
    SELECT CalanderDate + 1 FROM Calender
    WHERE CalanderDate + 1 <= @endDate
)
SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) 
FROM Calender
OPTION (MAXRECURSION 0)

Problem

Just want to select all dates between cureent date and first day of month . I want to use fill all dates in a temp table ``` declare @temp table ( ddate datetime ) ``` I have tried with `with cte` and i don't want to use while loop becuase i want to avoid while in stored procedure . For example as today is `11-oct-2012` so temp table should have 11 rows starting from `1-oct-2012` to `11-oct-2012`

Original source