{"id":139,"date":"2018-07-07T18:00:14","date_gmt":"2018-07-07T18:00:14","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=139"},"modified":"2018-07-12T18:44:56","modified_gmt":"2018-07-12T18:44:56","slug":"dates-in-sql-server-the-last-x-of-the-month","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/","title":{"rendered":"Dates In SQL Server: The last X of the month"},"content":{"rendered":"

Finding the last X day of the month isn’t as easy as you might think. I learned this when people wanted to build a calendar for planning and automation.<\/p>\n

This is a neat addition to the article:\u00a0Dates In SQL Server: Weekdays & Weekends<\/a><\/p>\n

Create sample date data<\/h2>\n

From my previous article, the below SQL will create a date range collection. I’ve picked a date range where we have some months with Friday being the last weekday of the week and some where it isn’t:<\/p>\n

--Set the start of the week to Monday\r\nSET DATEFIRST 1;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create Date Table\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE date_t\r\n(\r\n  [TheDate]   DATE NOT NULL PRIMARY KEY,\r\n  [IsWeekend] INT  NOT NULL,\r\n  [IsWeekday] INT  NOT NULL,\r\n);\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create Date list\r\n-- Using Number sequence: https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/01\/dates-in-sql-server-create-sample-date-ranges\/\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateStart DATE = '01-JAN-18';\r\nDECLARE @DateEnd   DATE = EOMONTH('01-MAR-18');\r\n \r\nPRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);\r\nPRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);\r\n\r\nWITH number_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n), number_sequences AS (\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a,\r\n       t3.a AS t3a,\r\n       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation\r\n  FROM number_tbl t1\r\n  CROSS JOIN number_tbl t2\r\n  CROSS JOIN number_tbl t3\r\n), date_sequence AS (\r\nSELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]\r\n  FROM number_sequences\r\n WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd\r\n)\r\nINSERT\r\n  INTO date_t\r\n      ([TheDate], [IsWeekend], [IsWeekday])\r\nSELECT [TheDate],\r\n       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 1 ELSE 0 END AS [IsWeekend],\r\n       CASE WHEN DatePart(dw, [TheDate]) IN (6, 7) THEN 0 ELSE 1 END AS [IsWeekday]\r\n  FROM date_sequence\r\n;<\/pre>\n
The questions<\/h1>\n
This is a result of some questions people asked me and proved an interesting challenge to create a neat solution for. This is around managing monthly processes, detecting if the present day meets the requirement, but also for planning in the future:<\/p>\n
    \n
  1. Can we flag if a date is the last Friday of the month?<\/li>\n
  2. Is there a way of detecting when it’s the last weekday of the month?<\/li>\n<\/ol>\n
    The solution<\/h1>\n
    For this I’ve calculated the working day number as in my previous post, but I’ve also put a count together to highlight a given day of the week by including it’s day of the month number.<\/p>\n
    In both cases I’m comparing the rows value with the highest value for the month, and outputting 1 if that is met. There are other ways to get this sort of result, but this is the most reusable that I’ve found. It’s easy to change the selection criteria and the windowing criteria:<\/p>\n
    --------------------------------------------------------------------------------\r\n-- Find the last X of the month\r\n--------------------------------------------------------------------------------\r\nWITH date_data AS (\r\nSELECT [TheDate],\r\n       DateName(dw, [TheDate]) AS TheDate_day_name,\r\n       [IsWeekend],\r\n       [IsWeekday],\r\n       CASE [IsWeekday]\r\n           WHEN 1\r\n           THEN SUM(CAST([IsWeekday] AS INT)) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate])\r\n           ELSE NULL\r\n        END AS [WorkingDayNumber],\r\n       CASE DateName(dw, [TheDate])\r\n           WHEN 'Friday' -- Change this to find the day we are interested in\r\n           THEN SUM(1)                        OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate])\r\n           ELSE 0\r\n        END AS [DayToTrackNumber]\r\n  FROM date_t\r\n)\r\nSELECT [TheDate],\r\n       [TheDate_day_name],\r\n       [IsWeekend],\r\n       [IsWeekday],\r\n       CASE [TheDate] WHEN DateFromParts(YEAR(GetDate()), MONTH(GetDate()), DAY(GetDate())) THEN 'Today' ELSE '' END AS [DayMarker],\r\n     --Find last weekday of month\r\n       [WorkingDayNumber],\r\n       CASE [WorkingDayNumber]\r\n           WHEN MAX([WorkingDayNumber]) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]))\r\n           THEN 1\r\n           ELSE 0\r\n        END [IsLast_WorkingDay],\r\n     --Find last X day of month\r\n       [DayToTrackNumber],\r\n       CASE [DayToTrackNumber]\r\n           WHEN MAX([DayToTrackNumber]) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]))\r\n           THEN 1\r\n           ELSE 0\r\n        END [IsLast_DayToTrack]\r\n  FROM date_data\r\n;<\/pre>\n
    See it in action over at rextester:\u00a0SQL Server – Find the last X of the month<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    Finding the last X day of the month isn’t as easy as you might think. I learned this when people wanted to build a calendar for planning and automation. This is a neat addition to the article:\u00a0Dates In SQL Server: Weekdays & Weekends Create sample date data From my previous article, the below SQL will…<\/p>\n
    Read More  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[12,15],"class_list":["post-139","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-date","tag-windowing"],"yoast_head":"\nDates In SQL Server: The last X of the month - Rows Across The Lake<\/title>\n<meta name=\"description\" content=\"Finding the last X day of the month isn't as easy as you might think. I learned this when people wanted to build a calendar for planning, and to automation.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dates In SQL Server: The last X of the month - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"Finding the last X day of the month isn't as easy as you might think. I learned this when people wanted to build a calendar for planning, and to automation.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-07T18:00:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-07-12T18:44:56+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Dates In SQL Server: The last X of the month\",\"datePublished\":\"2018-07-07T18:00:14+00:00\",\"dateModified\":\"2018-07-12T18:44:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/\"},\"wordCount\":282,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"date\",\"windowing\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/\",\"name\":\"Dates In SQL Server: The last X of the month - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2018-07-07T18:00:14+00:00\",\"dateModified\":\"2018-07-12T18:44:56+00:00\",\"description\":\"Finding the last X day of the month isn't as easy as you might think. I learned this when people wanted to build a calendar for planning, and to automation.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/07\\\/07\\\/dates-in-sql-server-the-last-x-of-the-month\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dates In SQL Server: The last X of the month\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data & Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dates In SQL Server: The last X of the month - Rows Across The Lake","description":"Finding the last X day of the month isn't as easy as you might think. I learned this when people wanted to build a calendar for planning, and to automation.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/","og_locale":"en_GB","og_type":"article","og_title":"Dates In SQL Server: The last X of the month - Rows Across The Lake","og_description":"Finding the last X day of the month isn't as easy as you might think. I learned this when people wanted to build a calendar for planning, and to automation.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/","og_site_name":"Rows Across The Lake","article_published_time":"2018-07-07T18:00:14+00:00","article_modified_time":"2018-07-12T18:44:56+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Dates In SQL Server: The last X of the month","datePublished":"2018-07-07T18:00:14+00:00","dateModified":"2018-07-12T18:44:56+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/"},"wordCount":282,"commentCount":0,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["date","windowing"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/","name":"Dates In SQL Server: The last X of the month - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2018-07-07T18:00:14+00:00","dateModified":"2018-07-12T18:44:56+00:00","description":"Finding the last X day of the month isn't as easy as you might think. I learned this when people wanted to build a calendar for planning, and to automation.","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/07\/07\/dates-in-sql-server-the-last-x-of-the-month\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Dates In SQL Server: The last X of the month"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data & Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/139","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=139"}],"version-history":[{"count":8,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/139\/revisions"}],"predecessor-version":[{"id":181,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/139\/revisions\/181"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=139"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=139"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=139"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}