Get The First and The Last Day of The Month

Just saw a question from someone on a mailing list I join asking for how to get the transactions occurring for the last three months (in other words, three months from current date).


First, you may want to do something simple.
To get the first day and the last day of the month, we can use:

SELECT 
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1

If you play around a little bit, you can also get the first day and the last day of the previous month:

SELECT 
DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) - 1

To get the transactions for the last three months, we just need to modify the number a little bit.

SELECT
DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1

Then, just select the transactions occurring in between these ranges.

SELECT * FROM TableName WHERE TransactDate BETWEEN
DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0) AND
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1