SQL Get the last date of the month

Introduction

In this tip and trick i am going to write about how to get last day of a perticular month of passed date?

Background

We can get last day of a month by several manipulations and use of built in date functions of sql server.

Using the code

Here I have declared @InputDate DateTime variable to pass date then i have set @InputDate Manually you can pass it from codebehind ,then i have created small snippet for retrieving last day of passed date

 Collapse | Copy Code

declare @InputDate datetime
set @InputDate='06/26/2014' --MM/DD/YYYY Format
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@InputDate))),DATEADD(mm,1,@InputDate)),101) as ReturnedDate 

Here i have Convert date in MM/DD/YYYY format using

 Collapse | Copy Code

Convert(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@InputDate))),DATEADD(mm,1,@InputDate)),101)

here 101 return date in MM/DD/YYYY format you can convert it in different format for more information you can see this Article

Convert Datetime to string in many formats[^]

We can create function to return last date of a month in this way

 Collapse | Copy Code

 CREATE FUNCTION [dbo].[func_LastDate](@Date DATETIME)
returns nvarchar(100)
AS
BEGIN
    DECLARE @ReturnedDate nvarchar(100)
    select @ReturnedDate=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),103)

    return @ReturnedDate

END 

We can call this function in this way

 Collapse | Copy Code

 declare @Date varchar(100)
select @Date=  [dbo].[func_LastDate] (getdate())
print @Date 

History

Alternate version of SQL Get the last date of the month[^]