First and Last Day of Month Calculations in T-SQL
Handy few SQL snippets. I find that I need to look this up often (from my own script collection), so I might as well post it and I can just search my blog whenever I need it 🙂
[sql]
DECLARE @tdy DATETIME
SET @tdy = GETDATE()
DECLARE @FirstDay1MonthAgo DATETIME
DECLARE @LastDay1MonthAgo DATETIME
DECLARE @FirstDay2MonthAgo DATETIME
DECLARE @LastDay2MonthAgo DATETIME
DECLARE @FirstDay3MonthAgo DATETIME
DECLARE @LastDay3MonthAgo DATETIME
DECLARE @FirstDayCurrentMonth DATETIME
DECLARE @LastDayCurrentMonth DATETIME
SELECT
@FirstDayCurrentMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, @tdy), 0),
@LastDayCurrentMonth = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @tdy) + 1, 0)),
@FirstDay1MonthAgo = DATEADD(MONTH, DATEDIFF(MONTH, 0, @tdy) – 1, 0),
@LastDay1MonthAgo = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @tdy), 0)),
@FirstDay2MonthAgo = DATEADD(MONTH, DATEDIFF(MONTH, 0, @tdy) – 2, 0),
@LastDay2MonthAgo = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @tdy) – 1, 0)),
@FirstDay3MonthAgo = DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) – 3, 0),
@LastDay3MonthAgo = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @tdy) – 2, 0))
[/sql]
Thanks, Belle! Code snippets pretty much rock. Keep ’em coming!
Kind of a cool trick, too. Does this perform better than using datepart and convert? My guess is that it does but I’m wondering if you’ve already compared query plans, etc. Let me know. Maybe I’ll try and do a follow up post to see which is faster.
Your code is definitely cleaner than what I would write!
LikeLike