Click here to Skip to main content
Click here to Skip to main content
Go to top

Finding the nth day of the nth week

, 28 Jun 2007
Rate this:
Please Sign up or sign in to vote.
This SQL Server function helps to get the nth day of the nth week.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
QuestionLogic problem PinmemberHain Kurt12-Oct-11 9:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 28 Jun 2007
Article Copyright 2007 by harry_kochhar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid