SQL PUZZLE | The Last Friday of the Month Puzzle | Advanced SQL

In this puzzle you have to read the input date from a variable and provide us the last friday of the month using SQL. For more details please see the sample input and expected output.

Sample Input

(No column name)
2010-07-10 00:00:00.000

Expected Output

Last-Friday of Month
2010-07-30 00:00:00.000

Script – DDL and INSERT Sample Data

 ```-- DECLARE @StartDate AS DATETIME ='10-July-2010' DECLARE @DayName AS VARCHAR(20) = 'Friday' -- ```

SOLUTION – 1

 ```-- DECLARE @StartDate AS DATETIME ='10-July-2010' DECLARE @DayName AS VARCHAR(20) = 'Friday' SELECT DATEADD(d, -1 * ((DATEPART(dw,EOMONTH(@StartDate)) + 1) % 7), EOMONTH(@StartDate)) [Last-Friday of Month] -- ```

Output – 1

 ```-- Last-Friday of Month -------------------- 2010-07-30 (1 row affected) -- ```

SOLUTION – 2

 ```-- DECLARE @StartDate AS DATETIME ='10-July-2010' DECLARE @DayName AS VARCHAR(20) = 'Friday' ;WITH SingleDigits(Number) AS ( SELECT Number FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)) AS X(Number) ) ,Series AS ( SELECT (d1.Number+1) + (10*d2.Number) Number from SingleDigits as d1, SingleDigits as d2 ) SELECT TOP 1 DATEADD(d,Number-1,@StartDate) [Last-Friday of Month] FROM Series WHERE DATEADD(d,Number-1,@StartDate) <= EOMONTH(@StartDate) AND DATENAME(dw, DATEADD(d,Number-1,@StartDate)) = @DayName ORDER BY Number DESC -- ```

Output – 2

 ```-- Last-Friday of Month ----------------------- 2010-07-30 00:00:00.000 (1 row affected) -- ```

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/