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
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.
- 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.
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.
<DateInIntegerFormat> - <DaysToSubtract>
- The difference returned by the previous step is then subtracted from the
integer equivalent of the input date.
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.
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.