Getting first and last month date using sql server

Get Last Day of the Month Function

Get Last Day of the Month Function 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.