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)