SQL Server dates table: programatically find the last Sunday in a given month

Question

(SQL Server 2008 and above): I needed to update a dimDate table to include the following columns:

Last Sunday Of The Month:
Last Monday Of The Month:
Last Tuesday Of The Month:
Last Wednesday Of The Month:
Last Thursday Of The Month:
Last Friday Of The Month:
Last Saturday Of The Month:

Don't get me wrong; it is very simple to locate the last day of the month in SQL through the following piece of code:

CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime))) AS [LastDayOfTheMonth]

But I could not find anywhere on the Internet, and I admit to not spending more than 2 hours searching, where people openly showed how to identify the last (insert day name) of the month.

Thus, I solved the equation and am posting it here, in case it is useful for anyone else, or, in case anyone has a simpler method I could have used, but simply could not see it.

The dimDates table is populated through a prepopulated dimNumbers table:

 IF OBJECT_ID('dbo.dimNumbers') IS NOT NULL 
      DROP TABLE dbo.dimNumbers;

DECLARE @UpperBound INT = 1000000;

;WITH cteN(Number) AS
(
     SELECT 
         ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
     FROM 
         sys.all_columns AS s1
     CROSS JOIN 
         sys.all_columns AS s2
)
SELECT [Number] 
INTO ref.dimNumbers
FROM cteN 
WHERE [Number] <= @UpperBound;

CREATE UNIQUE CLUSTERED INDEX CIX_dimNumbers ON ref.dimNumbers([Number]);

And then the dim Dates table is populated through the following means. And yes, I am lazy and want SQL to perform ALL possible calculations.

DECLARE @YearsToPopulate INT = 130;

-- Use the Magic of SQL to identify 1 Jan and then 31st December at the various edges of the required date time frames.
DECLARE @StartDate DATE = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yyyy,-@YearsToPopulate,GETDATE())), 0);
DECLARE @EndDate DATE = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yyyy,@YearsToPopulate,GETDATE())) + 1, -1);

DECLARE @RecordsToCreate INT =  DATEDIFF(dd,@StartDate,@EndDate);

;WITH MyFullDateRange AS 
(
   SELECT TOP (@RecordsToCreate)  
       CAST(DATEADD(dd, Number, @StartDate) AS DATE) AS DayInTime
   FROM 
       ref.[dimNumbers]
) 
SELECT 
    --Insert Formulas here, using [DayInTime] as the Variable
    -- The Formulas I have used here are not the topic of this discussion.
FROM 
    MyFullDateRange

And so, it took me a few hours to prove, but I finally came up with a simple, repeatable pattern to discover the "last (insert day name) of the month".

NB: Place the following inside the above SELECT statement.

--Sunday [DayOfWeek] = 1.. Need to convert 1 to 0 <-> N + (7 - 1) % 7
DATEADD(DD,
        - ((DATEPART(dw,  DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))) + (7 - 1) ) % 7 ,
        CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))
    )
    AS [LastsSundayOfTheMonth]


--Monday [DayOfWeek] = 2.. Need to convert 2 to 0 <->  N + (7 - 2) % 7   
DATEADD(DD,
            - ((DATEPART(dw,  DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))) + (7 - 2) ) % 7    ,
            CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))
        )
        AS  [LastMondayOfTheMonth]


--Tuesday [DayOfWeek] = 3.. Need to convert 3 to 0 <->  N + (7 - 3) % 7
DATEADD(DD,
            - ((DATEPART(dw,  DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime))))  + (7 -3) ) % 7   ,
            CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))
        )
        AS [LastTuesdayOfTheMonth]

--Wednesday [DayOfWeek] = 4.. Need to convert 4 to 0 <-> N + (7 - 4) % 7
DATEADD(DD,
            - ((DATEPART(dw,  DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))) + (7 - 4) ) % 7   ,
            CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))
        )
        AS [LastWednesdayOfTheMonth]

--Thursday [DayOfWeek] = 5.. Need to convert 5 to 0 <-> N + (7 - 5 ) % 7
DATEADD(DD,
            - ((DATEPART(dw,  DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime))))  + (7 - 5) ) % 7   ,
            CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))
        )
        AS [LastThursdayOfTheMonth]

--Friday [DayOfWeek] = 6.. Need to convert 6 to 0 <-> N + (7 - 6 ) % 7
DATEADD(DD,
            - ((DATEPART(dw,  DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime))))  + (7-6) ) % 7   ,
            CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))
        )
        AS [LastFridayOfTheMonth]


--Saturday [DayOfWeek] = 7.. Need to convert 7 to 0 <-> N + (7 - 7 ) % 7
DATEADD(DD,
            - ((DATEPART(dw,  DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime))))  + (7-7) ) % 7   ,
            CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))
        )
        AS [LastsSaturdayOfTheMonth]

I hope this is useful for others, or that someone is able to point out a simpler way to perform these.

To expand on [Matts] Answer for SQL Server 2012:

The below code works for SQL Server 2012 and above, and hopefully clearly identifies how each day is identified. (if it isn't, let me know and I will clarify)

DECLARE @YearsToPopulate INT = 130;

-- Use the Magic of SQL to identify 1 Jan and then 31st December at the various edges of the required date time frames.
DECLARE @Date1 DATE = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yyyy,-@YearsToPopulate,GETDATE())), 0);
DECLARE @Date2 DATE = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yyyy,@YearsToPopulate,GETDATE())) + 1, -1);

DECLARE @RecordsToCreate INT =  DATEDIFF(dd,@Date1,@Date2);

WITH MyFullDateRange AS 
(
   SELECT TOP (@RecordsToCreate)  CAST(DATEADD(dd, Number, @Date1) AS DATE) AS DayInTime
   FROM ref.[dimNumbers]
) 

SELECT  DayInTime
    --Sunday [DayOfWeek] = 1.. Need to convert 1 to 0 <-> N + (0 - 1) % 7
    ,LastSundayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 1 + DATEPART(dw,EOMONTH(DayInTime))) % 7, EOMONTH(DayInTime))

    --Monday [DayOfWeek] = 2.. Need to convert 2 to 0 <->  N + (0 - 2) % 7   
    ,LastMondayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 2 + DATEPART(dw,EOMONTH(DayInTime))) % 7, EOMONTH(DayInTime))

    --Tuesday [DayOfWeek] = 3.. Need to convert 2 to 0 <->  N + (0 - 3) % 7   
    ,LastTuesdayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 3 + DATEPART(dw,EOMONTH(DayInTime))) % 7, EOMONTH(DayInTime))

    --Wednesday [DayOfWeek] = 4.. Need to convert 4 to 0 <->  N + (0 - 4) % 7   
    ,LastWednesdayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 4 + DATEPART(dw,EOMONTH(DayInTime))) % 7, EOMONTH(DayInTime))

    --Thursday [DayOfWeek] = 5.. Need to convert 2 to 0 <->  N + (0 - 5) % 7   
    ,LastThursdayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 5 + DATEPART(dw,EOMONTH(DayInTime))) % 7, EOMONTH(DayInTime))

    --Friday [DayOfWeek] = 6.. Need to convert 2 to 0 <->  N + (0 - 6) % 7   
    ,LastFridayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 6 + DATEPART(dw,EOMONTH(DayInTime))) % 7, EOMONTH(DayInTime))

    --Saturday [DayOfWeek] = 7.. Need to convert 2 to 0 <->  N + (0 - 7) % 7   
    ,LastSaturdayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 7 + DATEPART(dw,EOMONTH(DayInTime))) % 7, EOMONTH(DayInTime))

FROM MyFullDateRange;

I would be using this code, if I did not have to support SQL Server 2008.

More SQL Server 2012 onwards code:

Unfortunately, what I was hoping would be a good alternative, is not working. Bare in mind the solution needs to fit within a dimDate table. Any suggestions to improve this? The part I do not like about this solution, is I would need to perform a double trip to dimDate table to update it.

DECLARE @YearsToPopulate INT = 130;

DECLARE @Date1 DATE = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yyyy,-@YearsToPopulate,GETDATE())), 0);
DECLARE @Date2 DATE = DATEADD(yy, DATEDIFF(yy,0,DATEADD(yyyy,@YearsToPopulate,GETDATE())) + 1, -1);

DECLARE @RecordsToCreate INT =  DATEDIFF(dd,@Date1,@Date2);

WITH MyFullDateRange AS 
(
   SELECT TOP (@RecordsToCreate)  CAST(DATEADD(dd, Number, @Date1) AS DATE) AS DayInTime
   FROM ref.[dimNumbers]
) 
, CreateListOfDatesAndDOWs As 
(
    Select DayInTime AS DayInTime,  DatePART( DW , DayInTime ) AS DayNumber
    From MyFullDateRange
)

 Select DayInTime AS [currentDate]--SQL 2012 --   DateFromParts(Year(DayInTime),Month(DayInTime) , 1) AS [currentDate]
       ,LastSun = MAX(CASE WHEN  DayNumber=1 THEN DayInTime END)
       ,LastMon = MAX(CASE WHEN  DayNumber=2 THEN DayInTime END)
       ,LastTue = MAX(CASE WHEN  DayNumber=3 THEN DayInTime END)
       ,LastWed = MAX(CASE WHEN  DayNumber=4 THEN DayInTime END)
       ,LastThu = MAX(CASE WHEN  DayNumber=5 THEN DayInTime END)
       ,LastFri = MAX(CASE WHEN  DayNumber=6 THEN DayInTime END)
       ,LastSat = MAX(CASE WHEN  DayNumber=7 THEN DayInTime END)
 From  CreateListOfDatesAndDOWs

 Group By DayInTime --DateFromParts(Year(DayInTime),Month(DayInTime),1)--SQL 2012

As this returns the following resultset, which for a dimDate table, is not what we are after. (I need to figure out how to format a table!)

currentDate LastSun    LastMon    LastTue    LastWed    LastThu    LastFri    LastSat
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1886-01-01  NULL       NULL       NULL       NULL       NULL       1886-01-01 NULL
1886-01-02  NULL       NULL       NULL       NULL       NULL       NULL       1886-01-02
1886-01-03  1886-01-03 NULL       NULL       NULL       NULL       NULL       NULL
1886-01-04  NULL       1886-01-04 NULL       NULL       NULL       NULL       NULL
1886-01-05  NULL       NULL       1886-01-05 NULL       NULL       NULL       NULL
1886-01-06  NULL       NULL       NULL       1886-01-06 NULL       NULL       NULL
1886-01-07  NULL       NULL       NULL       NULL       1886-01-07 NULL       NULL
1886-01-08  NULL       NULL       NULL       NULL       NULL       1886-01-08 NULL
1886-01-09  NULL       NULL       NULL       NULL       NULL       NULL       1886-01-09
1886-01-10  1886-01-10 NULL       NULL       NULL       NULL       NULL       NULL
1886-01-11  NULL       1886-01-11 NULL       NULL       NULL       NULL       NULL
1886-01-12  NULL       NULL       1886-01-12 NULL       NULL       NULL       NULL
1886-01-13  NULL       NULL       NULL       1886-01-13 NULL       NULL       NULL
1886-01-14  NULL       NULL       NULL       NULL       1886-01-14 NULL       NULL
1886-01-15  NULL       NULL       NULL       NULL       NULL       1886-01-15 NULL
1886-01-16  NULL       NULL       NULL       NULL       NULL       NULL       1886-01-16
1886-01-17  1886-01-17 NULL       NULL       NULL       NULL       NULL       NULL
1886-01-18  NULL       1886-01-18 NULL       NULL       NULL       NULL       NULL
1886-01-19  NULL       NULL       1886-01-19 NULL       NULL       NULL       NULL
1886-01-20  NULL       NULL       NULL       1886-01-20 NULL       NULL       NULL
1886-01-21  NULL       NULL       NULL       NULL       1886-01-21 NULL       NULL
1886-01-22  NULL       NULL       NULL       NULL       NULL       1886-01-22 NULL
1886-01-23  NULL       NULL       NULL       NULL       NULL       NULL       1886-01-23
1886-01-24  1886-01-24 NULL       NULL       NULL       NULL       NULL       NULL
1886-01-25  NULL       1886-01-25 NULL       NULL       NULL       NULL       NULL
1886-01-26  NULL       NULL       1886-01-26 NULL       NULL       NULL       NULL
1886-01-27  NULL       NULL       NULL       1886-01-27 NULL       NULL       NULL
1886-01-28  NULL       NULL       NULL       NULL       1886-01-28 NULL       NULL
1886-01-29  NULL       NULL       NULL       NULL       NULL       1886-01-29 NULL
1886-01-30  NULL       NULL       NULL       NULL       NULL       NULL       1886-01-30
1886-01-31  1886-01-31 NULL       NULL       NULL       NULL       NULL       NULL
1886-02-01  NULL       1886-02-01 NULL       NULL       NULL       NULL       NULL
1886-02-02  NULL       NULL       1886-02-02 NULL       NULL       NULL       NULL
1886-02-03  NULL       NULL       NULL       1886-02-03 NULL       NULL       NULL
1886-02-04  NULL       NULL       NULL       NULL       1886-02-04 NULL       NULL
1886-02-05  NULL       NULL       NULL       NULL       NULL       1886-02-05 NULL
1886-02-06  NULL       NULL       NULL       NULL       NULL       NULL       1886-02-06

please tag your dbms as well and also the version. – Vamsi Prabhala Dec 03 '16 at 23:34 — Vamsi Prabhala, Dec 03 '16 at 23:34

John Cappelletti · Answer 1 · 2016-12-04T00:58:08.573

1

Here is another quick option. This will return each last Weekday for each month

Using an ad-hoc tally table, and the window function Row_Number().

Just for fun you can have another field count up 1st, 2nd ...Monday of the Month, Quarter, Year

Edit - Added the final conditional aggregation

Declare @Date1 date = '2000-01-01'
Declare @Date2 date = DateAdd(YY,130,@Date1)
;with cte0(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
     ,cteD(D) As (Select Top (DateDiff(DD,@Date1,@Date2)) cast(DateAdd(DD,Row_Number() over (Order By (Select NULL))-1,@Date1) as date) From cte0 N1, cte0 N2, cte0 N3, cte0 N4, cte0 N5, cte0 N6) -- 1 Million
     ,cte     As (
                    Select Date=D
                          ,DOW       = DateName(DW,D) 
                          ,DOWPosNeg = Row_Number() over (Partition By Year(D),Month(D),DateName(DW,D) Order by D Desc)
                    From cteD
                 )
 Select Date    = DateFromParts(Year(Date),Month(Date),1)
       ,LastSun = max(case when DOW='Sunday'    then Date else null end)
       ,LastMon = max(case when DOW='Monday'    then Date else null end)
       ,LastTue = max(case when DOW='Tuesday'   then Date else null end)
       ,LastWed = max(case when DOW='Wednesday' then Date else null end)
       ,LastThu = max(case when DOW='Thursday'  then Date else null end)
       ,LastFri = max(case when DOW='Friday'    then Date else null end)
       ,LastSat = max(case when DOW='Saturday'  then Date else null end)
 From  cte
 Where DOWPosNeg = 1
 Group By DateFromParts(Year(Date),Month(Date),1)
 Order BY 1

Returns

edited Dec 04 '16 at 00:58

answered Dec 04 '16 at 00:01

John Cappelletti

79,615
7
44
66

It is an interesting concept, but would not fit as part of a dimDate table. – Drooten Dec 04 '16 at 00:08
Oh, I just realised what you did. You have simply calculated what the last day of the month was. This is not what I am after (that was a simple calculation). I want to know, For each possible Weekday, Monday AND Tuesday AND Wednesday AND Thursday AND Friday AND Saturday AND Sunday, I need to find out what the DATE was, but only the Last Saturday of that month, or the Last Wednesday of that month. So there has to be a minimum of 7 columns representing those week days. – Drooten Dec 04 '16 at 00:18
@Drooten I think you missed what I was trying to illustrate. See updated answer with the conditional aggregation.. In this case 1,560 months int 0.078 ms – John Cappelletti Dec 04 '16 at 00:33
@vkp You are too kind. Thanks – John Cappelletti Dec 04 '16 at 00:37
Is it just an accident that sometimes the columns return incorrect values? ie row 1: LastTues = LastThurs. – Drooten Dec 04 '16 at 00:50
@Drooten Yup. Copy Error. You will Change DOW='Tuesday' for LastTue – John Cappelletti Dec 04 '16 at 00:56
Although, not sure about the up vote, as it doesn't do what I asked. Matt's answer is closer. – Drooten Dec 04 '16 at 04:32
@Drooten Perhaps someone saw a nugget of value for something other than your exact needs, which clearly I'm not understanding – John Cappelletti Dec 04 '16 at 13:15
They key question was: CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, [DateParameter])))), DATEADD(MM, 1, [DateParameter]))) AS [LastDayOfTheMonth] : can you improve this formula using SQL 2008 code. Thanks – Drooten Dec 05 '16 at 11:44
sorry wrong block of code. try this one: --Tuesday [DayOfWeek] = 3.. Need to convert 3 to 0 <-> N + (7 - 3) % 7 DATEADD(DD, - ((DATEPART(dw, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime)))) + (7 -3) ) % 7 , CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, DayInTime)))), DATEADD(MM, 1, DayInTime))) ) AS [LastTuesdayOfTheMonth] – Drooten Dec 05 '16 at 11:54

score 0 · Accepted Answer · edited May 23 '17 at 10:30

0

SQL server 2012+ (because of use of EOMONTH())

DATEADD(DAY, 0 - (@@DATEFIRST - 1 + DATEPART(dw,EOMONTH(DateCol))) % 7, EOMONTH(DateCol))

This formula will work no matter what the DATEFIRST value is set to.

Working Example (links showing it works http://rextester.com/ZHJL66222):

DECLARE @Table AS TABLE (DateCol DATE)
INSERT INTO @Table VALUES ('1/15/2016'),('2/15/2016'),('3/15/2016'),
    ('4/15/2016'),('5/15/2016'),('6/15/2016'),('7/15/2016'),('8/15/2016'),
    ('9/15/2016'),('10/15/2016'),('11/15/2016'),('12/15/2016')


;WITH cte AS (
    SELECT
       *
       ,LastSundayOfMonth = DATEADD(DAY, 0 - (@@DATEFIRST - 1 + DATEPART(dw,EOMONTH(DateCol))) % 7, EOMONTH(DateCol))
    FROM
       @Table
)

SELECT
    *
    ,CheckToEnsure = DATENAME(dw,LastSundayOfMonth)
FROM
    cte

For SQL 2008 can switch EOMONTH() to something like this:

DATEADD(DAY, - DATEPART(DAY,DateCol),DATEADD(MONTH,1,DateCol))

For Other Days of the week this can be adapted as well check out this other answer: https://stackoverflow.com/a/40942693/5510627

edited May 23 '17 at 10:30

Community

1
1

answered Dec 03 '16 at 23:51

Matt

13,833
2
16
28

Nice for Sunday. What of the Other days, Mondays through to Saturday. Raised a good point, that I have to deal with SQL 2008, hence I did not use EOMONTH. Although you made me realise I left out the code for my Sunday also. – Drooten Dec 03 '16 at 23:57
basically same pattern as you discovered which is (7 - Desired DATEFIRST Value - 1). I actually answered then deleted this yesterday because I wasn't sure it was a good answer because I too was working on a date dimm :) http://stackoverflow.com/a/40942693/5510627 – Matt Dec 04 '16 at 00:04
@Drooten and now you have me re thinking my date dimm and might go back to add in Last X day of the week column. I have 145 columns right now though......any way you cut it a date dimm is a pain especially when you go to count weeks.... – Matt Dec 04 '16 at 00:07
This is why I added each and every day of the week, and showed the formula within the formula, so anyone can look at it and understand why one formula works for Sunday (7 - 1) or Monday (7-2). Sure, We may be able to 'basically use the same parttern', but I'm not exactly sure where that pattern is. Do you have any suggestions, as this is an improvement over what I came up with. – Drooten Dec 04 '16 at 00:11
I am happy to share my dimDate table with you Matt. as you said, it grows every time someone asks for 'something really simple'. grrrr. My answer to new fields quite often is NO. Especially if it can be calculated from the dimDate table. Thanks for the contribution. – Drooten Dec 04 '16 at 00:13
@Drooten thanks for the offer I would like to bounce mine off someone as well sometime. got to run in a minute. As far as the pattern I honestly took the idea from another answer (credited in my other answer) and modified. I get the concept but yeah not sure I am adding too much more. I answered before reading everything and then saw you posted both a question and an answer. great minds think alike though. – Matt Dec 04 '16 at 00:18
@Drooten I don't know if you are doing this but one of the advices I saw and picked up was to actually use ranking functions on an update and set the DowOfWeek In Month so that combined wtih DayOfWeek Name would give me 1st , 2nd, wed. Thursday, etc. I then applied this to week counts for month, Quarter, Year (Fiscal and Calendar) as well. We operate a resort so I am probably going to use the method to compare more similar periods rather than by straight date for year over year etc. – Matt Dec 04 '16 at 00:19
1

When I get to work tomorrow, I'll send you through my code. Its 2008 Compatible, however, it is NOT 145 columns. Only columns ratified by my work. I use no ranking functions in my code. I have no doubt you may need it for that many columns. – Drooten Dec 04 '16 at 00:23

score 0 · Answer 3 · answered Dec 04 '16 at 18:54

When I think of a dates table, it has all sorts of things about the date precomputed. For instance, here's one I whipped up just now for this question:

create table dbo.dimDate (
    CalendarDate date not null primary key,
    DateYear as year(CalendarDate) persisted,
    DateMonth as month(CalendarDate) persisted,
    DateDay as day(CalendarDate) persisted,
    DateDOW tinyint not null
);

Whether you want to do all of that stuff with persisted columns... up to you. But my point is that they're there as data, rather than computed at runtime. Once you have something like the above, you can get what you're looking for with the following query:

with cte as (
    select *, 
        row_number() over (
            partition by DateYear, DateMonth, DateDow
            order by CalendarDate desc
        ) as RankedDow
    from dbo.dimDate
)
select *
from cte
where RankedDow = 1
order by CalendarDate;

And, in the spirit of persisting that data, I'd make a new column in the dimDate column (something like LastDOWOfMonth as a bit) and persist the results of the above! Then, you can just do a simple query on the dimDate like

select CalendarDate
from dbo.dimDate
where DateYear = 2016
   and DateMonth = 12
   and DOW = 5
   and LasDOWOfMonth = 1;

Yes I seem to take it for granted that everyone has read Kimball's Data Warehouse books and understand the logic of a dimTable. — Drooten, Dec 05 '16 at 11:52

SQL Server dates table: programatically find the last Sunday in a given month

3 Answers3