Tuesday, July 6, 2010

How to get day name and count between date range

ALTEr FUNCTION [dbo].[GetDayName]
(
@StartDate Datetime ,
@EndDate Datetime
)
RETURNS @TblIBB table ([Date] Date, [dayName] varchar(15))
As
BEGIN
insert into @TblIBB
Select [DATE],DATENAME ( dw ,[DATE] ) [DayName]

from
(
SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )

[Date] = dateadd(DAY,ROW_NUMBER()

OVER(ORDER BY c1.name),

DATEADD(DD,-1,@StartDate))

FROM [master].[dbo].[spt_values] c1
) t1
return
End