Contact Me

Nirav Prabtani

Mobile : +91 738 308 2188

Email :

Nirav Prabtani

Wednesday, 28 May 2014

SQL Get the last date of the month


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


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
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
here 101 return date in MM/DD/YYYY format you can convert it in different format for more information you can see this Article
We can create function to return last date of a month in this way
 CREATE FUNCTION [dbo].[func_LastDate](@Date DATETIME)
returns nvarchar(100)
    DECLARE @ReturnedDate nvarchar(100)
    select @ReturnedDate=CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),103)

    return @ReturnedDate

We can call this function in this way
 declare @Date varchar(100)
select @Date=  [dbo].[func_LastDate] (getdate())
print @Date 


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

No comments :

Post a Comment