Get First and Last Day of Month
Just a quick post with two T-SQL functions to compute the first and last day of the month for any DATETIME. These have been incredibly useful on a daily basis and I figured that I would pass them along.
Keep in mind that these are inline scalar-value functions and will be evaluated for every row in a result set. This isn’t so bad when you only have a few rows, but when you have a million rows, it’s expensive. It’s much better to use the inline SQL computation if you need to work on a large number of results.
The third function will compute the first instance of the longest month between two dates. This is often used in utility billing to determine which month the bill should be applied to. An example would be a bill from January 1 to February 28. This bill would fall into January. If the bill were from January 15th to August 28th, the bill would be for March. It’s a more specific case than the first two, but still very useful in context.
CREATE FUNCTION dbo.GetFirstDayOfMonth (@Day DATETIME)
CREATE FUNCTION [dbo].[GetLastDayOfMonth] (@Day DATETIME)
CREATE FUNCTION [dbo].[ComputeFirstLongestMonth]
DECLARE @FirstDate DATETIME;
DECLARE @LastDate DATETIME;
DECLARE @FirstLongestMonth DATETIME;
DECLARE @MonthDiff INT;
DECLARE @counter INT;
DECLARE @DateDiff INT;
DECLARE @PrevDateDiff INT;
SET @counter = 0;
SET @DateDiff = 0;
SET @PrevDateDiff = 0;
SET @FirstDate = @StartDate;
SELECT @MonthDiff = DATEDIFF(mm, @StartDate, @EndDate);
WHILE @counter <= @MonthDiff
SET @LastDate = dbo.GetLastDayOfMonth(@FirstDate);
SET @DateDiff = DATEDIFF(dd, @FirstDate, @LastDate);
IF @DateDiff > @PrevDateDiff
SET @FirstLongestMonth = @FirstDate;
SET @PrevDateDiff = @DateDiff;
SET @FirstDate = dbo.GetFirstDayOfMonth(DATEADD(mm,1, @FirstDate));
SET @counter = @counter + 1;
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.