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)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(mm,DATEDIFF(mm,0,@Day),0);
END;
CREATE FUNCTION [dbo].[GetLastDayOfMonth] (@Day DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(ss,-1,DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@Day),0)));
END;
CREATE FUNCTION [dbo].[ComputeFirstLongestMonth]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
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
BEGIN
SET @LastDate = dbo.GetLastDayOfMonth(@FirstDate);
SET @DateDiff = DATEDIFF(dd, @FirstDate, @LastDate);
IF @DateDiff > @PrevDateDiff
BEGIN
SET @FirstLongestMonth = @FirstDate;
SET @PrevDateDiff = @DateDiff;
END
ELSE
BREAK;
SET @FirstDate = dbo.GetFirstDayOfMonth(DATEADD(mm, 1, @FirstDate));
SET @counter = @counter + 1;
END
RETURN @FirstLongestMonth;
END
