1

Given a date lets say Aug 2, 2019 which is first friday of the month. what would be the first friday of next month (sept 6th). Now lets say I have sept 26th which is the last thur of the month. what date would it be for the last thur of next month. I have to do this in MSSQL.

Uday Patel
  • 11
  • 1
  • "*I have to do this in MSSQL."* Great! Good luck and please do share the answer when you have it. If you get stuck, please do edit this to ask a question, and show your attempts, along with describing why what you have doesn't work. Thanks. – Thom A Sep 16 '19 at 14:05

1 Answers1

0

What would you do for the option of September 12th? Sounds like you have two separate scenarios.

In either case, I would use a Date Table or Calendar Table if you have one. If not, set one up. I highly recommend it. Ed Pollock does a great article on what a date table setup would look like here: Designing a Calendar Table. SQLServer.Info has a good example of the table here: Table Example. Second example for good measure Table Setup

Table Setup. This is how it should look. Or at least something similar. You can add more columns to do more comparisons.

Once you have your calendar table set up, its a matter of comparing the date you want to the table.

This is how you can grab the first week of the next month based on a date table

DECLARE @Date DATE = '8/2/2019';

SELECT       [dt].Date
       FROM  [Datetbl] AS [dt]
       WHERE [dt].[Weekday] = DATEPART([weekday], DATEADD(day, -1, @Date)) -- is same day of the week
             AND [dt].Month = DATEPART(month, DATEADD(MONTH, 1, @Date)) -- Grab the month number for "Next Month"
             AND [dt].Year = DATEPART(year, DATEADD(MONTH, 1, @Date)) -- Grab year of next month since that will be different for December
             AND [dt].[WeekOfMonth] IN
     (SELECT       MIN([WeekOfMonth])
             FROM  [Datetbl] AS [dt1]
             WHERE [dt1].[Weekday] = DATEPART([weekday], DATEADD(day, -1, @Date))
                   AND [dt1].Month = DATEPART(month, DATEADD(MONTH, 1, @Date))
                   AND [dt1].Year = DATEPART(year, DATEADD(MONTH, 1, @Date))
     ); -- returns Sept 6

This is how you would go about getting the last weekday of the month based on a date table

DECLARE @Date DATE = '9/26/2019';

SELECT       [dt].Date
       FROM  [Datetbl] AS [dt]
       WHERE [dt].[Weekday] = DATEPART([weekday], DATEADD(day, -1, @Date)) -- is same day of the week
             AND [dt].Month = DATEPART(month, DATEADD(MONTH, 1, @Date)) -- Grab the month number for "Next Month"
             AND [dt].Year = DATEPART(year, DATEADD(MONTH, 1, @Date)) -- Grab year of next month since that will be different for December
             AND [dt].[WeekOfMonth] IN
     (SELECT       MAX([WeekOfMonth])
             FROM  [Datetbl] AS [dt1]
             WHERE [dt1].[Weekday] = DATEPART([weekday], DATEADD(day, -1, @Date))
                   AND [dt1].Month = DATEPART(month, DATEADD(MONTH, 1, @Date))
                   AND [dt1].Year = DATEPART(year, DATEADD(MONTH, 1, @Date))
     ); -- returns Oct 31st

Hope this helps.

Eric
  • 13
  • 4