|
|
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:
-
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.
-
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.
-
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.
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.
|