Introduction
This is a generic function created in SQL Server 2000. It takes three parameters and returns the nth day of the nth week in DateTime format.
Using the code
The code is useful to those who are looking to get the date of the nth day of the nth week of a given month.
To get the required output, the end user / DBA is required to pass the week number in numeric form. The second argument consists of the day of the week. Valid values can be between 1 to 7, where 1 represents Sunday, 2 represents Monday, and so on. The third argument is any date of the month for which the required data is needed.
For example: if a user needs to search for the second Friday of July, 2007, then the Exec statement required for it would be:
select dbo.getWeekDay(2,6,'2007-07-10')
The above code will return 2007-07-13 00:00:00.000.
CREATE function getWeekDay( @var_weeknum int, @var_weekday int, @var_date datetime)
returns datetime
as
BEGIN
declare @cnt int
declare @startDay int
declare @DayDiffrence int
declare @ReturnDate datetime
set @cnt = 1
set @startday = datepart(dw, dateadd(mm, datediff(mm, 0, @var_Date),0))
set @DayDiffrence = @var_weekday - @startday
set @ReturnDate = dateadd(mm, datediff(mm, 0, @var_date),0)
if(@DayDiffrence > 0)
begin
set @ReturnDate = dateadd(d,@DayDiffrence,@ReturnDate)
set @ReturnDate = dateadd(wk,@var_weeknum - 1,@ReturnDate)
end
else
begin
set @ReturnDate = dateadd(d,7 - (@DayDiffrence * -1),@ReturnDate)
set @ReturnDate = dateadd(wk,@var_weeknum - 1,@ReturnDate)
end
return @ReturnDate
end
In the above code, @Startday
will initially contain the starting day of the given month. For the above example, it will return 01 (Sunday). @DayDiffrence
will carry the difference of @startday
and @var_weekday
which is passed as an argument. In our example, @DayDiffrence
will have 01 - 06 = -05 as its value. @ReturnDate
will be initialized with the starting date of the month. This means that this variable will initially carry the first date of the given month. As per our example above, @ReturnDate
will have '2007-07-01 00:00:00'.
@DayDiffrence
would have a positive value if the starting day is before the required day. In this case, it is only required to add the difference to the starting date, and deducting 1 from it will give us the required day. For the nth week, we need to add @var_weeknum - 1
to it.
@DayDiffrence
would have a negative value if the starting day is after the required day. In this case, it is only required to add the mod of @DayDiffrence
to the starting date, and the logic of the nth week is the same as that for the case of the positive value.
History
- 2007-06-28 10:30:00 CST: First version.