Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

Tagged as

SQL Get the Last Date of the Month

, 27 May 2014
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "SQL Get the last date of the month"

Introduction

In this tip and trick, I am going to write about how to get the last day of a particular month of passed date?

Background

We can get the 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 a small snippet for retrieving the 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:

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 take a look at 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)
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:

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

History

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nirav Prabtani
Web Developer Satva Infotech
India India



Nirav Prabtani

I am a software engineer at Satva Infotech, Database Architect and Designer /Technical Architect/Analyst
Programmer in Microsoft .NET Technologies & Microsoft SQL Server with more than
2 years of hands on experience.

I love to code....!!! Smile | :)


My recent past includes my work with the education domain as a technical business
requirement analyst, database architect & designer and analyst programmer; just
love my involvement with the world of knowledge, learning and education and I think
I know quite well what I want to do in life & in my career. What do I like? Well,
ideation, brainstorming, coming up with newer and more creative ways of doing things;
each time with an enhanced efficiency. An item in my day's agenda always has a task
to look at what I did yesterday & focus on how I can do it better today




Contact Me

Nirav Prabtani


Mobile : +91 738 308 2188

Email : niravjprabtani@gmail.com

My Blog:
Nirav Prabtani

 
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralMy vote of 1 PinprofessionalAmol_B2-Jun-14 2:01 
GeneralRe: My vote of 1 PinprofessionalNirav Prabtani2-Jun-14 2:03 
QuestionHere's a simple solution PinmemberJoe Gakenheimer28-May-14 3:20 
QuestionEOMONTH PinmemberKikkik28-May-14 0:09 
Questionwhy so complicated? PinmemberPeter BCKR27-May-14 22:49 
QuestionFYI PinprofessionalMycroft Holmes27-May-14 22:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 28 May 2014
Article Copyright 2014 by Nirav Prabtani
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid