Get First Day of the Month Function¶

Getting the first day of the month is straight-forward. It's a matter of replacing the day portion of the given date with 1. Discussed below are 2 ways of getting the first day of the month for a given input date.

First Variant¶

The first variant of getting the first day of the month implements the simple task of replacing the day portion of the given input date with 1.

CREATE FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + 
                CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

END
GO

Description¶

This function simply puts together the year, month and 1 as the day separating them with slashes (/) in the "YYYY/MM/DD" format.

You may wonder, why use the "YYYY/MM/DD" format when there are other formats available. Well, this format provides the least confusion for SQL Server because it cannot be mistaken for a "YYYY/DD/MM" format because such format does not exist. If the function formatted the date in the "MM/DD/YYYY" format, a different result may be returned if the default date format of SQL Server is "DD/MM/YYYY". A date of "02/01/2005" will be interpreted as January 2, 2005 instead of February 1, 2005.

Second Variant¶

The second variant in getting the first day of the month does not make use of any date format. It makes use of the numeric representation of the input date to calculate for the first day of the month.

CREATE FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) - 
               (DAY(@pInputDate) - 1) AS DATETIME)

END
GO