How can I select the first day of a month in SQL?
Solution 1:
SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth
Solution 2:
In addition to all the above answer, a way based on a function introduced in sql 2012
SELECT DATEFROMPARTS(YEAR(@mydate),MONTH(@mydate),1)
Solution 3:
Starting with SQL Server 2012:
SELECT DATEADD(DAY,1,EOMONTH(@mydate,-1))
Solution 4:
The casting of a string (i.e. "5/1/2009") to datetime is certainly more legible but we found code a while back that would return the first of the month...
DECLARE @Date DATETIME
//...
SELECT DATEADD(mm, DATEDIFF(mm,0,@Date), 0)