Split the given date into days

Question

I am writing a Stored Procedure. Basically i need take year and month information from user and doing something. But my problem is i don't detect given month has how many days. Let me explain with example;

User give me year and month as i said;

@year = 2015

@month = 07

So i must create rows like;

2015-07-01

2015-07-02

.....

2015-07-31

My plan is adding these rows one by one to another temp table.

Last status of my SP is;

ALTER PROCEDURE [dbo].[Usp_CreateStats]
(
 @Year VARCHAR (40),
 @Month VARCHAR (40)
 )
AS
BEGIN

DECLARE @StartTime VARCHAR (10)
DECLARE @EndTime VARCHAR (10)
SET @StartTime = '00:00:00'
SET @EndTime = '23:59:59'

DECLARE @Peoples TABLE (name VARCHAR(50))
INSERT INTO @Peoples (name) select distinct name from USERINFO

DECLARE @Dates TABLE (date VARCHAR(50))



END

I hope explained correctly.

Answer 1

You can use recursive CTE.

Declare @Year as varchar(5) = '2015'
Declare @Month as varchar(5) = '05'

Declare @DateOfMonth as DateTime =  @Month + '-01-' + @Year

Declare @startdate datetime, @enddate datetime

SET @startdate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@DateOfMonth),0))
SET @enddate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateOfMonth)+1,0))
SET @enddate = DATEADD(day,-1,@enddate)

;With DateSequence( Date ) as
(
    Select @startdate as Date
        union all
    Select dateadd(day, 1, Date)
        from DateSequence
        where Date < @enddate
)

--select result
Select * from DateSequence option (MaxRecursion 1000)

Answer 2

Use Recursive CTE,

DECLARE @year  VARCHAR(4) = '2015',
        @month VARCHAR(2)= '02',
        @date  DATETIME

SET @date= CONVERT(DATETIME, @year + '-' + @month + '-01');

WITH cte
     AS (SELECT @date AS dt
         UNION ALL
         SELECT Dateadd(dd, 1, dt) AS dt
         FROM   cte
         WHERE  dt < Dateadd(dd, -1, Dateadd(mm, Datediff(mm, 0, @date) + 1, 0)))
SELECT *
FROM   cte 

Answer 3

I've changed the year and month variables to int data type, the name of your date column to dates_date, and used convert and a while loop with dateadd function to populate the @dates table:

ALTER PROCEDURE [dbo].[Usp_CreateStats]
(
 @Year int,
 @Month int
 )
AS
BEGIN

DECLARE @StartTime VARCHAR (10),
        @EndTime VARCHAR (10),
        @Date date
SET @StartTime = '00:00:00'
SET @EndTime = '23:59:59'

SET @Date = CONVERT(date, right('0000' + cast(@year as char(4)), 4) + right('00' + cast(@month as char(2)), 2)+ '01', 112)

DECLARE @Peoples TABLE (name VARCHAR(50))
INSERT INTO @Peoples (name) select distinct name from USERINFO

DECLARE @Dates TABLE (dates_date date)

while month(@date) = @Month    
begin
    insert into @Dates (dates_date) values (@Date)
    set @Date = dateadd(day, 1, @date)
end

END

Answer 4

DECLARE @Month int= 7 
Declare @Year int = 2015
Declare @MonthStart date = CAST(@Year  as varchar(4)) +  Right('00' +    Cast(@Month as varchar(2)), 2)  + '01'


DECLARE @DaysInMonth int = (SELECT
                    datediff(day, dateadd(day, 1-day(@MonthStart),     @MonthStart),
                    dateadd(month, 1, dateadd(day, 1-day(@MonthStart), @MonthStart))))

Declare @Dates TABLE (date date) 

DECLARE @Counter int = 0 
WHILE @Counter < @DaysInMonth 
BEGIN
INSERT INTO @Dates SELECT DATEADD(Day, @Counter,  @MonthStart)

SET @Counter = @Counter + 1 
END

Answer 5

Here is another (faster) tally solution:

DECLARE @year   INT = 2015,
        @month  INT = 7

DECLARE @start datetime = dateadd(m, (@year- 1900) * 12 + 7, -31)

SELECT TOP(datediff(d, @start, dateadd(m, 1, @start))) 
  dateadd(d, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, @start)
FROM 
  (VALUES(0),(0),(0),(0),(0),(0)) a(n)
   CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0)) b(n)

Answer 6

CTE you can use:

DECLARE @year               INT = 2015,
        @month              INT = 7,
        @first_day_of_month DATETIME,
        @last_day_of_month  DATETIME 

select @first_day_of_month = cast(cast(@year as varchar(4))+'/'+cast(@month as varchar(2))+'/'+'01' as date)
select @last_day_of_month = dateadd(mm,datediff(mm,0,@first_day_of_month)+1,0)-1

;with mycte as 
(
select @first_day_of_month as src
union all
select dateadd(dd,1,src) from mycte
where src < @last_day_of_month

)select * from mycte