Monday, March 27, 2006

Use DATEADD And DATEDIFF To Get The First And Last Day Of The Month

Here are a couple different ways to get the first day of the current month, the first day of next month and finally the last day of the current month
Nothing special really, it is just a matter of applying datediff and dateadd
This was a question that I answered in the Transact-SQL MSDN Forum

The first day of the current month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+0, 0)
SELECT CONVERT(DATETIME,(CONVERT(VARCHAR(6),GETDATE(),112) + '01'))

The first day of next month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0)
SELECT DATEADD(mm,1,CONVERT(DATETIME,(CONVERT(VARCHAR(6),GETDATE(),112) + '01')))

The last day of the current month
SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0))
SELECT DATEADD(d,-1,DATEADD(mm,1,CONVERT(DATETIME,(CONVERT(VARCHAR(6),GETDATE(),112) + '01'))))

No comments: