facility9

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