CODEWITHSUNDEEP
sql-servert-sqldate
Sam
Sam

Reputation: 145

Creating a date from Week of month and Day of week in SQL server

I have to get/create date from the user input of week of month (week number in that month - 1st,2nd,3rd,4th and last) and day of week (sunday,monday..) in SQL server.

Examples: 4th Sunday of every month, Last Friday of every month, First Monday etc.

I was able to do it easily in .net but SQL server does seem limited in the date functions.

I am having to use lot of logic to get the date. To calculate the date using the above two parameters I had to use lot of datepart function.

Any suggestions on how to come up with the optimal SQL query for such a function?

Upvotes: 3

Views: 3578

Answers (2)

Brian Pressler
Brian Pressler

Reputation: 6713

Here is a general formula:

declare @month as datetime --set to the first day of the month you wish to use
declare @week as int  --1st, 2nd, 3rd...
declare @day as int   --Day of the week (1=sunday, 2=monday...)

--Second monday in August 2015
set @month = '8/1/2015'
set @week = 2
set @day = 2

select dateadd(
    day,
    ((7+@day) - datepart(weekday, @month)) % 7 + 7 * (@week-1),
    @month
    )

You can also find the last, 2nd to last... etc with this reverse formula:

--Second to last monday in August 2015
set @month = '8/1/2015'
set @week = 2
set @day = 2

select
    dateadd(
    day,
    -((7+datepart(weekday, dateadd(month,1,@month)-1)-@day)) % 7 - 7 * (@week-1),
    dateadd(month,1,@month)-1
    )

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

I created a function other day for another OP GET Month, Quarter based on Work Week number

This function takes the current year as default it can be further modified to take Year as a parameter too.

an extension to that function can produce the results you are looking for ....

WITH X AS
(
SELECT TOP (CASE WHEN YEAR(GETDATE()) % 4 = 0 THEN 366 ELSE 365 END)-- to handle leap year 
      DATEADD(DAY 
             ,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
             , CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '0101' )      
                      DayNumber
  From master..spt_values
 ),DatesData AS(
SELECT DayNumber [Date]
      ,DATEPART(WEEKDAY,DayNumber) DayOfTheWeek
      ,DATEDIFF(WEEK, 
            DATEADD(WEEK, 
               DATEDIFF(WEEK, 0, DATEADD(MONTH, 
                           DATEDIFF(MONTH, 0, DayNumber), 0)), 0)
              , DayNumber- 1) + 1 WeekOfTheMonth
FROM X )
SELECT * FROM DatesData
WHERE DayOfTheWeek   = 6  -- A function would expect these two parameters
 AND  WeekOfTheMonth = 4  -- @DayOfTheWeek and @WeekOfTheMonth

Upvotes: 1

Related Questions