Select all dates between the first day of the month and the current date.

Question

Simply select all dates between the confirmation date and the first day of the month. I want to use filling all dates in temp table

declare @temp table ( ddate datetime ) 

I tried with with cte and I do not want to use while loop because I want to avoid during the stored procedure.

For example, today is 11-oct-2012

therefore, the temp table should have 11 rows, starting from 1-oct-2012 to 11-oct-2012

Answer 1

try it

 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) 

Answer 2

 declare @temp table (ddate datetime); insert @temp select DATEDIFF(d,0,GetDate()-Number) from master..spt_values where type='p' and number < DatePart(d,Getdate()) order by 1; 

Answer 3

use current_date add the days of each generated series to the days, the series can subtract current_day from the first day of the month to 0, for example today is June 02, the series can be (-1, 0).

Answer 4

Try the following code:

 DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' + + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy DECLARE @endDate DATETIME= CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '31/' + + CAST(YEAR(GETDATE()) AS VARCHAR) -- 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)