How-To Get Last Day of a Month using T-SQL (Transact-SQL)
Here is another useful function to determine the last day of the month. I put this into a function so that I can use it in different stored procedures and views. It can be inserted in a select or where clause or even a group by clause. You can also use it as a way to initialize a variable.
I always go by the rule of divide and conquer which is how I will explain this function. First declare a variable to hold the Last Day of the Month value : @LastDayOfMonth as Datetime because we want to return a date.
Next, I am going to start with the innermost value which is the number of months as an int (integer) between 0 and the month value of @SelectedDate. This is the date you will pass to the function. So to get the number of months I use the following bit of code: DATEDIFF(m,0,@SelectedDate)+1 which gets the number of months "m" between 0 and @SelectedDate +1 which will return the first day of the next month at midnight.
Let's say for example that @SelectedDate is 09/24/2010 then the above code will return: 1329
After that I will get a new date that adds the number of months to 0 (or nothing), which is this bit of code DATEADD(mm, DATEDIFF(m,0,@SelectedDate)+1,0) which will give the following date if I continue with my previous example : 2010-10-01 00:00:00.000.
Now all I have left to do is move the date back one (1) second to get the date of the previous day, which works out to be the last of day of the preceding month. To accomplish this I use the this next bit of code: DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0) ) which produces the following date using the same value as in preceding examples : 2010-09-30 23:59:59.000.
For aesthetics I remove the minutes, seconds and nanoseconds with this last bit of code, select convert(char(10), DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0,@SelectedDate)+1,0)),101)) that returns the following value : 09/30/2010 when I use the date form the preceding examples.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: klanguedoc
-- Create date: 9/24/2010
-- Description: Get last day of the month
CREATE FUNCTION dbo.GetLastDayOfMonth
declare @LastDayOfMonth datetime
set @LastDayOfMonth = (select convert(char(10), DATEADD(s,-1, DATEADD(mm, DATEDIFF(m,0,@SelectedDate)+1,0)),101))
-- Return the result of the function
This is another very handy function that can be used over and over again. This and the preceding article on getting the Week Ending should be part of the Transact SQL API, in my opinion. Who knows maybe it will show up in the next version.