First Day of Month« View all SQL articles
December 01, 2006
The following code calculates the first day of the current month.
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
The database journal does an excellent job explaining each part of the SQL statement.
You may substitute getdate() and calculate the first day of any date.
In this example I will use 12/10/2007 and calculate the first day of the month.
--The result is 2007-12-01 00:00:00.000
select DATEADD(mm, DATEDIFF(mm,0,'12/10/2007'), 0)
Let's take it a step further and calculate the first day of the next month.
We added DATEADD(month, 1,...)
select DATEADD(month, 1, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
To build on the original SQL statement, we can calculate the 2nd day of the month as well as the last day of the previous month.
--Change the 0 to 1 and you will get the 2nd day of the month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 1)
--Change the 0 to -1 and you will get the last day of the previous month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), -1)
|