2

Using SQL Server 2008

Based on a specified date, I need to find the date of either the next Friday or the last day of the month if the next Friday is in the next month. Would prefer inline SQL as opposed to a Function but will take what I can get.

Examples:

For the month of October 2013:

A date of 10/3/2013 would return 10/4/2013 (the next Friday)
A date of 10/14/2013 would return 10/18/2013 (the next Friday)
A date of 10/25/2013 would return 10/25/2013 (it is a Friday)
A date of 10/29/2013 would return 10/31/2013 (the last day of the month since the next Friday is in the next month)

Malachi
  • 3,205
  • 4
  • 29
  • 46
fsu1tm
  • 23
  • 1
  • 3
  • look at this one .. http://stackoverflow.com/questions/5984704/sql-the-sp-or-function-should-calculate-the-next-date-for-friday – AJP Oct 24 '13 at 14:37
  • This one just finds the next Friday. For 10/26/2013 it returns 11/1/2013. Need it to return 10/31/2013. Thanks though. – fsu1tm Oct 24 '13 at 15:07

1 Answers1

2

I'm not an expert in SQL server, but this should get you close. Put this in a SQL Function:

DECLARE @date DATETIME = '10/03/2013';

SELECT  MIN(Date) AS NextFridayOrEoMonth
FROM    ( SELECT    DATEADD(DAY, ( CASE DATEPART(DW, @date)
                                     WHEN 7 THEN 6
                                     ELSE 6 - DATEPART(DW, @date)
                                   END ), @date) AS Date
          UNION
          SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @date) + 1, 0)) AS Date
        ) AS dates;

EDIT: Actually, here it is as a function. Good luck!

CREATE FUNCTION dbo.NextFridayOrEoMonth ( @date DATETIME )
RETURNS DATETIME
    WITH SCHEMABINDING,
         RETURNS NULL ON NULL INPUT
AS 
    BEGIN


        DECLARE @result DATETIME;


        SELECT  @result = MIN(Date)
        FROM    ( SELECT    DATEADD(DAY, ( CASE DATEPART(DW, @date)
                                             WHEN 7 THEN 6
                                             ELSE 6 - DATEPART(DW, @date)
                                           END ), @date) AS Date
                  UNION
                  SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @date) + 1, 0)) AS Date
                ) AS dates;

        RETURN @result;
    END
GO  

SELECT  dbo.NextFridayOrEoMonth('10/3/2013') AS NextFridayOrEoMonth; -- 2013-10-04
SELECT  dbo.NextFridayOrEoMonth('10/5/2013') AS NextFridayOrEoMonth; -- 2013-10-11
SELECT  dbo.NextFridayOrEoMonth('10/14/2013') AS NextFridayOrEoMonth; -- 2013-10-18
SELECT  dbo.NextFridayOrEoMonth('10/25/2013') AS NextFridayOrEoMonth; -- 2013-10-25
SELECT  dbo.NextFridayOrEoMonth('10/26/2013') AS NextFridayOrEoMonth; -- 2013-10-31
SELECT  dbo.NextFridayOrEoMonth('10/29/2013') AS NextFridayOrEoMonth; -- 2013-10-31
GO

Note: Code reviews / comments appreciated.

bopapa_1979
  • 8,949
  • 10
  • 51
  • 76
  • The first part, which gets the next Friday, uses the solution that @AJP provided above in his comment: http://stackoverflow.com/questions/5984704/sql-the-sp-or-function-should-calculate-the-next-date-for-friday. It uses this solution for the last day of the month: http://stackoverflow.com/questions/16646585/sql-query-to-find-the-last-day-of-the-month. – bopapa_1979 Oct 24 '13 at 15:04
  • Neither work. The in-line SQL was returning Saturdays so I changed the first part to "DATEADD(d, 6 - DATEPART(dw, @date), @date) AS Date" (notice the 6 instead of 7) but when I put in a date of 10/26/2013, it returns 10/25/2013 instead of 10/31/2013. For the function, when I use the date of 10/26/2013, it also returns 10/25/2013. – fsu1tm Oct 24 '13 at 15:17
  • @user2916205 - I had already noticed the problem and corrected it before you added your comment. It works as posted, now. All four test cases you posted are included in the edited answer, and return the same dates you mentioned above. Sorry for the inconvenience. – bopapa_1979 Oct 24 '13 at 15:21
  • I retried both the in-line and the function again. When using 10/5/2013, both return 10/4/2013 instead of 10/11/2013. – fsu1tm Oct 24 '13 at 15:39
  • 1
    @fsu1tm LOL, you keep coming up with more test dates! Sorry, I'm just breezing through trying to answer people's SQL questions. I'm not doing production level testing, but OK... Here is one final update the solves ALL of the dates so far in this post. The function has been updated to include the outputs I'm getting so you can compare side by side. – bopapa_1979 Oct 24 '13 at 16:25
  • Works. Thanks a bunch. Will mark as solution if I can figure out how ;-) Newbie here. – fsu1tm Oct 24 '13 at 16:45
  • @fsu1tm - My pleasure, and Welcome to Stack Overflow!!! The moderators are EXCELLENT and will mark stuff as duplicates, point to existing resources, and leave constructive criticism on your questions, so don't hesitate to ask about whatever! – bopapa_1979 Oct 24 '13 at 18:05