T-SQL: Calculating MonthEnd

Posted on 22/08/2008. Filed under: Software Development |

A common task many SQL developers face is to return the monthend for a date range. The common question is: “I want to get all the monthend values for the year?” It is a simple business requirement that can easily be satisfied with some simple T-SQL programming in SQL Server 2005.
Here is how:
Firstly you need a numbers table and if you don’t have one already, I have the script below to create one:

CREATE TABLE [dbo].[TableOfNumbers](
[number] [int] NOT NULL,
CONSTRAINT [PK_TableOfNumbers] PRIMARY KEY CLUSTERED
( [number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Sticking to good SQL programming we create a clustered index on the table. Since it only has one column and the data type is int with no duplicate or null values, a surrogate key is overkill.

Secondly we need to populate the table with some numbers, so I used the cool new feature of SQL Server 2005 – the While statement. Note, that I only populated the table up to the number 10,000. This can be any value you choose.

DECLARE @i int
SET @i = 0
WHILE @i <= 9999 – (note this is 1 less than the final amount since it increments inside the while) BEGIN SET @i = @i + 1 INSERT INTO dbo.tableofnumbers (number) VALUES (@i) END


Now that the setup work is done we can start with answering the actual business question.
The code below will return all the monthend values for this year(2008), but you could specify any date range.

DECLARE @SDate datetime, @EDate datetime
SELECT @SDate = ‘1/01/2008′, @EDate = ’12/31/2008’
SELECT
dateadd(month, number – 1, — Months in range
dateadd(day, -1 * datepart(day, @SDate), — Last Day of (prior) month
dateadd(month, 1, — Following month
dateadd(day, datediff(day, 0, @SDate), 0)))) — Zero out time portion of inputs
as MonthEnd
FROM dbo.TableOfNumbers — table with numbers in it
WHERE number <= datediff(month, @SDate, @EDate) + 1– Range set


This post was sparked by an article on http://www.sqlservercentral.com/ and well as personal experience. For a long discussion on this scenario you could find the article on the above website.

Advertisements

Make a Comment

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

  • Enter your email address to follow this blog and receive notifications of new posts by email.

  • Who’s visiting

  • South Africa

  • Tweets

    Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: