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

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: klanguedoc

-- Create date: 9/24/2010

-- Description: Get last day of the month

-- =============================================

CREATE FUNCTION dbo.GetLastDayOfMonth

(

@SelectedDate datetime

)

RETURNS datetime

AS

BEGIN

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

RETURN @LastDayOfMonth


END

GO

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.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working