Find the days of the week in a certain period of time

/*

**********************************************

Program Id:fun_getdaysofweek

Purpose: To find the number of days in a certain period of time during a Sunday period

Author:jesse

date:2011.02.19

**********************************************

*/

Go

Create Function dbo. Fun_getdaysofweek (

@DateS smalldatetime,--Start time

@DateE smalldatetime,--End

@WeekValue int)--Day of the week:-Sunday,-Monday ... 7-Saturday

Returns nvarchar (100)

As

Begin

Declare

@sResult nvarchar (100),--Results description

@nResult int,--Result days

@nWeekValue int,--Start date is day of the week (1-Sunday,-Monday ... 7-Saturday)

@nDays int,--Number of days in the time period

@nBeforeDays int,--the number of days from the start date to the first matching date

@sWeekName varchar (20),--the name of the day of the week to be asked

@tmpDate smalldatetime

Set @[email protected]% 7

If @WeekValue =0

Set @WeekValue =7

--If the start date is greater than the end date, swap

If @DateS > @DateE

Select @[email protected],@[email protected],@[email protected]

--the number of weeks to get the start date, regardless of setting (1-Sunday,-Monday ... 7-Saturday)

Select @nWeekValue = (Datepart (DW, @DateS) + (@ @Datefirst%7))%7

If @nWeekValue =0

Set @nWeekValue =7

Set @sWeekName =datename (Dw,dateadd (d,@[email protected], @DateS))

If @WeekValue >[email protected]

Set @[email protected] @nWeekValue

Else

Set @nBeforeDays =7-abs (@[email protected])

--To obtain the number of days within the time period

Select @nDays =datediff (d, @DateS, @DateE) +1

If @nDays < @nBeforeDays

Select @nResult =0

Else

Select @nResult =ceiling ((@[email protected])/7.0)

Set @sResult =n ' date range: ' + CONVERT (varchar (), @DateS, +) + N ' to ' +convert (varchar), @DateE, 120) + '

[Email protected] +n ' Total ' +cast (@nResult as varchar) +n ' Day '

Return @sResult

End

Go

--Test example

SELECT dbo. Fun_getdaysofweek (' 2011-03-01 ', ' 2011-05-31 ', 1)

--Running results

/*

Date range: 2011-03-01 to 2011-05-31 Sunday total 13 days

*/

Find the days of the week in a certain period of time