Finding the nth day of the nth week

Download source code - 0.5 KB

Introduction

This is a generic function created in SQL Server 2000. It takes three parameters and returns the n^th day of the n^th week in DateTime format.

Using the code

The code is useful to those who are looking to get the date of the n^th day of the n^th 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 n^th 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 n^th week is the same as that for the case of the positive value.