How can I select the first day of a month in SQL?

Dynamic date manipulation

Sometimes, you're not just interested in the current month. You might have a particular date, @mydate, and you need to know the start of the month for that date. With SQL Server, we can leverage the DATEADD and DATEDIFF functions for precise date calculations:

-- Calculate the start of a month like NASA calculates rocket launches!
SELECT DATEADD(mm, DATEDIFF(mm, 0, @mydate), 0) AS FirstDayOfMonth;


Here, the literal 0 corresponds to the base date (1900-01-01), yielding the first day of @mydate's month.

Less coding, more functionality

Let's walk away from those hardcoded and less maintainable dates. From SQL Server 2012 and onward, we've been blessed with the DATEFROMPARTS and EOMONTH functions. They simplify date calculations and totally skip manual string manipulation:

-- Simpler than remembering your first date!
SELECT DATEADD(day, 1, EOMONTH(@mydate)) AS NextMonthFirstDay;

-- Look back, but don't turn into a pillar of salt!
SELECT EOMONTH(@mydate, -1) AS PreviousMonthLastDay;


Utilizing these built-in functions guarantees slim margin of error and peak performance.

In the wild: Different SQL ecosystems

Working with multiple SQL databases calls for adaptability. A look at how to get the first day of the current month in common SQL environments:

• We've seen SQL Server's DATEFROMPARTS and EOMONTH functions in action.
• Oracle, has the mighty TRUNC function.
• MySQL utilizes DATE_FORMAT and the helpful LAST_DAY function:

-- One step forward, two steps back
SELECT LAST_DAY(CURDATE()) + INTERVAL 1 DAY;


• PostgreSQL users can rely on DATE_TRUNC:

-- Shout "truncating!" before running this in a library
SELECT DATE_TRUNC('month', CURRENT_DATE)::date;


Understanding your toolset across SQL preferences takes you a notch higher in writing versatile and scalable code.