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]

Advertisements

One comment

  • 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!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s