Function to return all days in a month

Hi,

What I need to do is to join a table with all days in a range that the users inform with another table that has the values so I can have a montly report separated day by day...I don't want to create the table manually cause the range is different...

I tried to do with cursor but it's to slow..

Any better solution?

Thanks

You don't say what version of SQL Server you are using. If you are using SQL Server 2005 then you can use a recursive common table expression to do what you want. Here is a sample:

Code Snippet

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

-- Use a common table expression to loop over a set of years

;WITH DayList (DayDate) AS

(

SELECT @.RepMonth

UNION ALL

SELECT DATEADD(d, 1, DayDate)

FROM DayList

WHERE (DayDate < DATEADD(d, -1, DATEADD(m, 1, @.RepMonth)))

)

SELECT *

FROM DayList

|||

OK thanks. It solves for my other program....But I still need it to sql2000... =)

Thanks

|||

Been thinking about it since posting previously and I came up with the following code which works if you don't have SQL Server 2005. It depends upon having a table which simply contains the values from 0 to at least 30 (giving 31 records for 31 days). I put up to 32 in to show check the logic worked for any month - and that it can have any number of records in it. In the sample this is a table variable - but in a real system this would probably just be a little standard table.

Code Snippet

DECLARE @.ValueList table

(

ValueID int

)

SET NOCOUNT ON

INSERT INTO @.ValueList Values ( 0)

INSERT INTO @.ValueList Values ( 1)

INSERT INTO @.ValueList Values ( 2)

INSERT INTO @.ValueList Values ( 3)

INSERT INTO @.ValueList Values ( 4)

INSERT INTO @.ValueList Values ( 5)

INSERT INTO @.ValueList Values ( 6)

INSERT INTO @.ValueList Values ( 7)

INSERT INTO @.ValueList Values ( 8)

INSERT INTO @.ValueList Values ( 9)

INSERT INTO @.ValueList Values (10)

INSERT INTO @.ValueList Values (11)

INSERT INTO @.ValueList Values (12)

INSERT INTO @.ValueList Values (13)

INSERT INTO @.ValueList Values (14)

INSERT INTO @.ValueList Values (15)

INSERT INTO @.ValueList Values (16)

INSERT INTO @.ValueList Values (17)

INSERT INTO @.ValueList Values (18)

INSERT INTO @.ValueList Values (19)

INSERT INTO @.ValueList Values (20)

INSERT INTO @.ValueList Values (21)

INSERT INTO @.ValueList Values (22)

INSERT INTO @.ValueList Values (23)

INSERT INTO @.ValueList Values (24)

INSERT INTO @.ValueList Values (25)

INSERT INTO @.ValueList Values (26)

INSERT INTO @.ValueList Values (27)

INSERT INTO @.ValueList Values (28)

INSERT INTO @.ValueList Values (29)

INSERT INTO @.ValueList Values (30)

INSERT INTO @.ValueList Values (31)

INSERT INTO @.ValueList Values (32)

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

SELECT DATEADD(d, ValueID, @.RepMonth)

FROM @.ValueList

WHERE (DATEADD(d, ValueID, @.RepMonth) < DATEADD(m, 1, @.RepMonth))

Put an order by on the selects if you need the records in a particular order.

|||

You might want to give consideration to using a calendar table; you can find an article that describes this type of table here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

GREAT !!! Thanks..you're the master....

Do you have msn....I'll add you to never forget !(or add me...it's my nickname @.hot....

|||

You may find having a 'Calendar' table to be constantly useful.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

create function udf_MakeDatesTable(

@.dtInput smalldatetime

,@.Months tinyint = 1

)

/*

this function will allow you get a table of dates

for any range of months you like,

starting at the input date.

See below for the usage

*/

returns @.TableOfDates table(dtTemp smalldatetime)

as

begin

declare @.dtEnd smalldatetime

--declare @.dtInput smalldatetime

--set @.dtInput = '1/5/2005 05:12'

--This line will strip out the Time component

set @.dtinput = convert(varchar,@.dtInput,106)

--Find our end date. Always @.Months after @.dtInput

set @.dtEnd = dateadd(m, @.Months, @.dtInput)

--declare @.TableOfDates table(dtTemp smalldatetime)

while @.dtInput < @.dtend

begin

insert into @.TableOfDates select @.dtInput

set @.dtInput = @.dtInput + 1

end

return

end

go

select * from dbo.udf_MakeDatesTable('1/15/2005',1)