Tip of the Day : SQL Server Database Design - Twitter Profile and Followers
Get First Day of the Month Function

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

Description

In a short summary, what this user-defined function is doing is to subtract the day part of the input date less 1 day from the input date.  So if today is April 14, 2005, to get the first day of the month we simply need to subtract 13 days from it, which is achieved by subtracting 1 from the day part of the input date (14 - 1 = 13).  Here's how this logic is achieved by the user-defined function:

  1. CAST(@pInputDate AS DECIMAL(12, 5)) - First step is to convert the input date into its decimal format.  DateTime data types are stored internally by Microsoft SQL Server as two 4-byte integers.  The whole number part of the datetime in the decimal format represents the number of days before or after the base date, January 1, 1900.
  2. FLOOR(<DateInDecimal>) - Second step is to remove the time portion of the date.  This can easily be done by using the FLOOR function, which returns the largest integer less than or equal to the given numeric expression.
  3. DAY(@pInputDate) - 1 - Third step is to get the day part of the input date.  We will be using this to subtract from the current date.  The day part is subtracted by 1 before the difference is subtracted from the integer format of the input date.
  4. <DateInIntegerFormat> - <DaysToSubtract> - The difference returned by the previous step is then subtracted from the integer equivalent of the input date.
  5. CAST(<DateInInteger> AS DATETIME) - Lastly, the resulting integer value is converted back to DateTime data type and returned by the function.

You may be thinking, why not simply use CAST(@pInputDate AS INT) instead of the longer code of FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))).  If you just cast the input date to integer, this gets rounded off.  So if the time part of the input date is after 12:00PM, this will be rounded up to the next day.

You may wonder why not use the following code instead of the ones above:

SET @vOutputDate = DATEADD(DD, 1 - DAY(@pInputDate), @pInputDate)

This basically performs the same logic as above, which is subtracting the day part of the input date less 1 day from the input date.

This will work only if the input date does not have a time portion.  If there is a time part in the input date, then the output date will be the first day of the month with the same time as the input date.  The previous code will work whether or not time is included in the input date.

Usage

Here's an example of how to use this user-defined funcation:

SELECT * FROM [dbo].[Orders]
WHERE [OrderDate] >= [dbo].[ufn_GetFirstDayOfMonth] ( GETDATE() )

This statement returns all orders starting from the first of the month.