T-SQL Script: Finding the First Monday of the Month

I recently had a reporting requirement to find the first Monday of the month. I used the dateadd/datediff method introduced to me by Itzik Ben-Gan. This is the single line of code, but it might help to break it down into multiple lines of code.

SELECT DATEADD(wk, DATEDIFF(wk, '20101206', dbo.FormatDateTime( GETDATE(), '[MM]/07/[YYYY]')), '20101206')

1) Working from the inside out, we have this line:

dbo.FormatDateTime(GETDATE(), '[MM]/07/[YYYY]')

This line uses a function that I wrote (or stole off the Internet) called FormatDateTime. Here's a blog post of mine about it: FormatDateTime Script

This line assumes that the first Monday of the month needs to be in the first seven days. It chooses the seventh day of the month of the current month.

2) Next comes this line:

DATEDIFF(wk, '20101206', dbo.FormatDateTime(GETDATE(), '[MM]/07/[YYYY]'))

This line chooses an anchor date that is the first Monday of a chosen month. 12/6/2010 was the first Monday of December 2010. It then takes that seventh day of the current month and finds out how many weeks (wk) are between 12/6/2010 and the seventh of the current month. If today is January 26th, 2011, then the answer will be 5 weeks.

Finally, when you put the whole statement together, it adds five weeks only to 12/6/2010, which will be the first Monday of January 2011 which is 1/3/2011.

If you want to find out the first Monday of next month, or any month, then replace the getdate() call with a dateadd call like this:

DATEADD(m, 1, GETDATE())

which will create this:

SELECT DATEADD(wk, DATEDIFF(wk, '20101206', dbo.FormatDateTime(DATEADD(m, 1, GETDATE()), '[MM]/07/[YYYY]')), '20101206')