/* Required Results: Date of day
Known conditions: The year, month, day of the week, in the month of the week
For example: Hope to find out 2004-11-22
Known conditions: 2004, November, Monday, this day in November is the fourth week
DECLARE @year numeric (4), @month numeric (2), @weekday varchar (6), @week numeric (1)
Set @year =2004
Set @month =11
Set @weekday = ' Monday '
Set @week =4
*/
Alter function requestdate
(@year Numeric (4), @month numeric (2), @weekday varchar (6), @week Numeric (1))
returns datetime AS
Begin
DECLARE @date1 varchar (10)--Get the date of the first day of the month
DECLARE @month1 varchar (2)
If @month <10
Set @month1 = ' 0 ' LTrim (RTrim (str (@month))
Else
Set @month1 =ltrim (str (@month)) (RTrim)
Set @date1 =ltrim (RTrim (str (@year))) '-' @month1 '-' ' 01 '
DECLARE @num1 numeric (3)
DECLARE @num2 numeric (3)
DECLARE @num3 numeric (3)
Set @num1 =datepart (WW, @date1)--Gets the number of weeks of the first day of the month in that year
Set @num1 = @num1 @week-2-the number of weeks that are required for the month is worse than the week of the first week of the year
Set @num2 =datepart (WW, @num1, ' 2004-01-01 ')--get the date of the week code
Select @num3 =case @weekday
When ' Sunday ' then 1
When ' Monday ' then 2
When ' Tuesday ' then 3
When ' Wednesday ' then 4
When ' Thursday ' then 5
When ' Friday ' then 6
When ' Saturday ' then 7
End
return DateAdd (DD, @num3-datepart (Dw,dateadd (WW, @num1, ' 2004-01-01 ')), DateAdd (WW, @num1, ' 2004-01-01 '))
End
--Select dbo. RequestDate (2004,11, ' Monday ', 4)
