Here are 2 approachs to do this.
Declare @Days Table (Days int);
DECLARE @Date DATETIME
SET @Date = '2012-08-27'
DECLARE @Count INT
SET @Count = 1
DECLARE @DaysCount INT
SELECT @DaysCount = datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))
WHILE @Count <= @DaysCount
BEGIN
INSERT INTO @Days
SELECT @Count
SET @Count = @Count + 1
END
SELECT * FROM @Days
The second approach would be to create a function that will return the number of days for a given date.
found the function
here[
^]
CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime)
IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0
AND
YEAR(@myDateTime) % 100 != 0)
OR
(YEAR(@myDateTime) % 400 = 0)
THEN 29
ELSE 28 END
END
RETURN @rtDate
END
and then call the function to get the number of days in a month like this
Declare @Days Table (Days int);
DECLARE @i INT
SET @i = 1
WHILE @i <= (SELECT dbo.udf_GetNumDaysInMonth('2012-08-27') NumDaysInMonth)
BEGIN
INSERT INTO @Days
SELECT @i
SET @i = @i + 1
END
SELECT * FROM @Days