There’s an EOMONTH function in SQL Server, why no Start-of-Month function?

Dec 15, 2020 | SQL coding

I responded to a posted question about the EOMONTH() function, which was: if there’s an EOMONTH function, why isn’t there a start of month function.

The reason is that DATEFROMPARTS does the trick.  It was added in SQL 2012 along with EOMONTH and DATETIMEFROMPARTS and several other date and date/time functions.

It’s very simple to use, supply the year, month and day.  Since the start-of-month is always the first, it’s the constant 1.

declare @target datetime = getdate()
select EOMONTH(@target) end_of_month, DATEFROMPARTS (year(@target), month(@target), 1) start_of_month

And that gives us the answer:

end_of_month start_of_month
------------ --------------
2020-12-31 2020-12-01

(1 row affected)