SQL SERVER – Simple Method to Find FIRST and LAST Day of Current Date

I calculate the FIRST and LAST Day of Current Date using explicit date boundaries. The historical sample starts on October 28, 2017.

DECLARE @Date datetime = '20171028';
SELECT @Date AS GivenDate,
       CONVERT(date, DATEADD(day, 1 - DAY(@Date), @Date)) AS FirstDayOfMonth,
       EOMONTH(@Date) AS LastDayOfMonth;

Interpret the first and last day for the current date

The expected boundaries are 2017-10-01 and 2017-10-31. DATEADD moves to day one, while converting to date removes time. EOMONTH returns the last day as a date. It requires SQL Server 2012 or later.

My old arithmetic retained time when the input was not midnight. It also did not generalize to date subtraction syntax. The explicit expressions avoid those assumptions. Substitute the current server date when required.

For a timestamp column, filter from the first day up to, but excluding, the first day of the next month. Comparing with midnight on the last day can omit that entire remaining day. The linked methods cover older releases.

Check the calendar and the reporting boundary

The example answers a monthly question. Its input is one point within October, and its outputs identify that month’s two calendar boundaries. These results do not identify the beginning and end of the input’s individual day. Make that distinction explicit when using the expressions in a report.

Month lengths also vary. For February 17, 2020, the corresponding boundaries are February 1 and February 29 because 2020 is a leap year. For February 17, 2021, they are February 1 and February 28. A fixed number of days cannot represent both cases correctly. The built-in month-end calculation follows the calendar.

For a December report, the exclusive upper boundary belongs to January of the following year. That is expected. It allows every timestamp within December to be considered while excluding the following month’s values. Keeping the upper comparison exclusive avoids inventing a final fractional second that depends on the column’s precision.

I test a non-midnight input as well as an ordinary calendar value before reusing older arithmetic. Check the returned types and the intended business period separately from the display format. If the application groups UTC events by a local reporting period, decide the time-zone boundary before applying the filter.

Reference: Microsoft’s EOMONTH documentation.

Related reading

• Query to Find First and Last Day of Current Month
• SQL SERVER – Script/Function to Find Last Day of Month
• SQL SERVER – Validating If Date is Last Day of the Year, Month or Day

A last-day date is not the end of that day, it is midnight when compared with a timestamp.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.