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

To generate the dates based on @year and @month, you can use a Tally Table.

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

DECLARE @startDate DATE = DATEADD(MONTH, @month - 1, DATEADD(YEAR, @year - 1900, CAST('19000101' AS DATE)))

;WITH Tally (n) AS
(   -- 100 rows
    SELECT TOP(31) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
)
SELECT
    DATEADD(DAY, N-1, @startDate)
FROM Tally t
WHERE
    DATEADD(DAY, N-1, @startDate) < DATEADD(MONTH, 1, @startDate)

SQL Fiddle

---

Note: Use the appropriate data type for your variables. In this case, @month and @year should be INT.

Answer 2

convert(varchar,OrderDate,111)='2015-07-01'