Recently, I received an interesting question from one of my readers. They asked if there was a ready-made function in SQL Server that could display whether a given date is the last day of the year, month, or week. For example, if a date is the last day of the year and the last day of the month, they wanted to display “Last Day of the Year”. Similarly, if a date is the last date of the month and the last day of the week, they wanted to display “Last Day of the Week”.

Unfortunately, there is no built-in function in SQL Server that directly provides this information. However, I have a solution that I have used for my personal needs, which accomplishes the same task.

Let’s take a look at an example:

-- Example of Year
DECLARE @Day DATETIME
SET @Day = '2014-12-31'

SELECT CASE 
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms, -3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, @Day) + 1, 0))) AS DATE) THEN 'Last Day of Year'
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @Day) + 1, 0)) AS DATE) THEN 'Last Day of Month'
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(s, -1, DATEADD(wk, DATEDIFF(wk, 0, @Day), 0)) AS DATE) THEN 'Last Day of Week'
    ELSE 'Day'
END

In this example, we are checking if the given date is the last day of the year, month, or week. We use the CASE statement to evaluate the conditions and return the corresponding result.

Similarly, you can use the same logic to check for the last day of the month or week. Here are a couple of examples:

-- Example of Month
DECLARE @Day DATETIME
SET @Day = '2014-06-30'

SELECT CASE 
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms, -3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, @Day) + 1, 0))) AS DATE) THEN 'Last Day of Year'
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @Day) + 1, 0)) AS DATE) THEN 'Last Day of Month'
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(s, -1, DATEADD(wk, DATEDIFF(wk, 0, @Day), 0)) AS DATE) THEN 'Last Day of Week'
    ELSE 'Day'
END

-- Example of Week
DECLARE @Day DATETIME
SET @Day = '2014-05-04'

SELECT CASE 
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms, -3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, @Day) + 1, 0))) AS DATE) THEN 'Last Day of Year'
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @Day) + 1, 0)) AS DATE) THEN 'Last Day of Month'
    WHEN CAST(@Day AS DATE) = CAST(DATEADD(s, -1, DATEADD(wk, DATEDIFF(wk, 0, @Day), 0)) AS DATE) THEN 'Last Day of Week'
    ELSE 'Day'
END

Feel free to use this logic in your own SQL Server queries to determine the last day of the year, month, or week. If you have any other smarter tricks or alternative solutions, please let me know, and I would be happy to share them here with due credit.