|
|
There are different ways of getting the last day of the month for a given input
date. The simplest way of doing this is by getting the first day of the
month for the given input date, add 1 month to that and then subtract 1 day to
it to get the last day of the month. Discussed below are 3 ways of
performing this task.
First Variant
The first variant in getting the last day of the month involves one of the ways
of getting the first day of the month given an input date. The simplest
way to get the first day of the month is by substituting the day part of the
input date with 1, as is implemented below. The resulting date is then
incremented by 1 month and then a day is subtracted to get the last day of the
month.
|
CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @vOutputDate DATETIME
SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
RETURN @vOutputDate
END
GO
Description
Here are the steps involved in getting the last day of the month as implemented
in the user-defined function above:
-
CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS
VARCHAR(2)) + '/01'
- This step simply gets the first day of the current month given the input
date. This is achieved by replacing the day part of the input date by
1. The date is formatted using the "YYYY/MM/DD" date format.
-
DATEADD(M, 1, @FirstDayOfTheMonth)
- The result of the previous step, shown here as @FirstDayOfTheMonth, is then
incremented by 1 month to get the first day of the following month.
-
DATEADD(DD, -1, @FirstDayOfTheNextMonth) - Lastly, a day is subtracted
from the previous step, the first day of the following month shown here as
@FirstDayOfTheNextMonth, to get the last day of the month.
|
|
Second Variant
The second variant in getting the last day of the month implements the same
algorithm as the first variant, which is to get the first day of the month for
the given input date, add a month to that then subtract one day. The only
difference with the second variant discussed below compared to the first
variant is on how the first day of the month is derived.
CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @vOutputDate DATETIME
SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) -
(DAY(@pInputDate) - 1) AS DATETIME)
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
RETURN @vOutputDate
END
GO
Description
In getting the first day of the month, this second variant did not make use of
any date format. Instead, it made use of the decimal equivalent of the
input date. The following outlines the logic behind this variant:
-
FLOOR(CAST(@pInputDate AS DECIMAL(12, 5)))
- The first step is to convert the input date into its decimal equivalent using
CAST. Then the decimal equivalent is converted to integer using the FLOOR
function, truncating the decimal value.
-
@DateInIntegerFormat - (DAY(@pInputDate) - 1)
- The result from the first step, which is referred here as
@DateInIntegerFormat, is then subtracted by the difference between the day part
of the input date and 1. This now gives as an integer value which is the
equivalent of the first day of the month.
-
CAST(@FirstDayOfTheMonthInInteger AS DATETIME)
- The integer equivalent of the first day of the month, referred here as
@FirstDayOfTheMonthInInteger, is then converted back to DATETIME.
-
DATEADD(M, 1, @FirstDayOfTheMonth)
- One month is then added to the result from the previous result giving us the
first day of the following month.
-
DATEADD(DD, -1, @FirstDayOfNextMonth) - Given the first day of the
following month, referred here as @FirstDayOfNextMonth, 1 day is subtracted to
it to get the desired output of the last day of the month for the given input
date.
Third Variant
The third variant in getting the last day of the month for the given input date
simply simplifies the code by putting the step in getting the first day of the
month into a separate function, called here as
[dbo].[ufn_GetFirstDayOfMonth].
CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @vOutputDate DATETIME
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1,
[dbo].[ufn_GetFirstDayOfMonth] (@pInputDate)))
RETURN @vOutputDate
END
GO
Description
Just like the previous two variants, this variant simply gets the first day of
the month for the given input date, add 1 month to it and subtract 1 day.
Here's a summary of the steps performed by this user-defined function:
-
[dbo].[ufn_GetFirstDayOfMonth] (@pInputDate)
- The first step calls the user-defined function that gets the first day of the
month given the input date.
-
DATEADD(M, 1, @FirstDayOfMonth)
- The next step is to get the first day of the following month.
-
DATEADD(DD, -1, @FirstDayOfNextMonth) - Lastly, given the first day of
the following month, 1 day is subtracted from it to get the last day of the
month.
Usage
Here's an example SELECT statement that uses this user-defined function and yet
another function, the [dbo].[ufn_GetFirstDayOfMonth], which gets the first day
of the month given an input date.
SELECT * FROM [dbo].[Employees]
WHERE [BirthDate] BETWEEN [dbo].[ufn_GetFirstDayOfMonth] ( GETDATE() ) AND
[dbo].[ufn_GetLastDayOfMonth] ( GETDATE() )
This SQL statement will retrieve all employees whose birthday falls within the
month.
|