gevjen gevjen - 3 months ago 16
SQL Question

Sql Date - Breaking End Of Month up

I have a query (below) that will start at a specific time. from that time i need to increment a week at a time (each new row will be a new week - going from sunday to saturday) i have this done - but the new wrinkle is that if it is the end of the month it needs to stop on that date and start up on the first of the month but still stop on that saturday. result set good/bad are listed below and also my query up to this point.

Bad results:

2016-05-22 2016-05-28
2016-05-29 2016-06-04


What I need:

2016-05-22 2016-05-28
2016-05-29 2016-05-31
2016-06-01 2016-06-04


Code:

BEGIN
DECLARE @StartDate DATE = DATEFROMPARTS(2016, 5, 22);
DECLARE @EndDate DATE = CAST(GETDATE() AS DATE);
DECLARE @Today DATE = @EndDate;
DECLARE @EndOfMonth DATE = @StartDate

; WITH [Dates] AS (
SELECT
@StartDate AS [StartDate],
DATEADD(DAY, 6, @StartDate) AS [EndDate]
UNION ALL
SELECT
DATEADD(DAY, 7, [StartDate]),
DATEADD(DAY, 7, [EndDate])
FROM [Dates]
WHERE DATEADD(DAY, 7, [StartDate]) <= @EndDate
)
SELECT
[tcw].[StartDate],
[tcw].[EndDate]
FROM [Dates] AS [tcw]
OPTION (MAXRECURSION 0)
END
GO

Answer

Given that you use DATEFROMPARTS, I'm assuming you are using SQL 2012 or later. I'm also relying on your existing logic with regards to determining the first and last dates. Based on this, replace your query with the following:

; WITH [Dates] AS (
    SELECT
        @StartDate AS [StartDate],
        DATEADD(DAY, 6, @StartDate) AS [EndDate]
    UNION ALL
    SELECT
        DATEADD(DAY, 7, [StartDate]),
        DATEADD(DAY, 7, [EndDate])
    FROM [Dates]
    WHERE   DATEADD(DAY, 7, [StartDate]) <= @EndDate
)
--  All weeks where all dates are within the same month
SELECT
    StartDate
   ,EndDate
FROM [Dates]
WHERE MONTH(StartDate) = MONTH(EndDate)
--  For weeks where all dates not within the same month, the last week in the month
UNION ALL SELECT
    StartDate
   ,EOMONTH(StartDate)
FROM [Dates]
WHERE MONTH(StartDate) <> MONTH(EndDate)
--  For weeks where all dates not within the same month, the first week in the (next) month
UNION ALL SELECT
    DATEADD(dd, 1, EOMONTH(StartDate))
   ,EndDate
FROM [Dates]
WHERE MONTH(StartDate) <> MONTH(EndDate)

The unions make it a bit awkward, but unless you're processing centuries with every pass it will run quickly enough. Note also that some "weeks" will contain one day, such as for July 31, 2016 through July 31, 2016.

-- Addenda, based on the comment ---------------------------------

The following query does this, but with a big warning…

; WITH cteDates AS (
    SELECT
        @StartDate AS StartDate,
        DATEADD(DAY, 6, @StartDate) AS EndDate,
        CASE
          WHEN DATEPART(dw, EOMONTH(@StartDate)) between 2 and 6 then 0  --  Assumes SET DATEFIRST is 1!
          ELSE 1
        END  AS MonthEndsOnWeekend
    UNION ALL
    SELECT
        DATEADD(DAY, 7, StartDate),
        DATEADD(DAY, 7, EndDate),
        CASE
          WHEN DATEPART(dw, EOMONTH(DATEADD(DAY, 7, StartDate))) between 2 and 6 then 0
          ELSE 1
        END
    FROM cteDates
    WHERE   DATEADD(DAY, 7, StartDate) <= @EndDate
)
--  All weeks where all dates are within the same month,
--  and all month-ending weeks where the last day of the month is Saturday or Sunday
SELECT
    StartDate
   ,EndDate
FROM cteDates
WHERE MONTH(StartDate) = MONTH(EndDate)
  OR MonthEndsOnWeekend = 1
--  For weeks where all dates not within the same month,
--  and the the last day of the month is NOT Saturday or Sunday,
--  the last week in the month
UNION ALL SELECT
    StartDate
   ,EOMONTH(StartDate)
FROM cteDates
WHERE MONTH(StartDate) <> MONTH(EndDate)
  AND MonthEndsOnWeekend = 0
--  For weeks where all dates not within the same month,
--  and the the last day of the month is NOT Saturday or Sunday,
--  the first week in the (next) month
UNION ALL SELECT
    DATEADD(dd, 1, EOMONTH(StartDate))
   ,EndDate
FROM cteDates
WHERE MONTH(StartDate) <> MONTH(EndDate)
  AND MonthEndsOnWeekend = 0

I use the DATEPART function to identify the day of week (Saturday, Sunday, etc). SQL will return a number for this function, where the number returned for day of week depends upon the setting of SET DATEFIRST. For my installations, we use the default where 1 = Monday. If you do not have complete control over the setting of SET DATEFIRST everywhere your code may ever be run, forever, then you may have problems outside the scope of this discussion. An alternative that I have used is to use DATENAME, which will return character strings, e.g. Saturday, Sunday, etc… but this has the same problem with regards to the setting of SET LANGUAGE.

(fyi, I also took out the [ ] and renamed the cte, because they were bugging me.)

Comments