SQL Query to find the last day of the month

Question

0.00/5 (No votes)

See more:

I need to find the last day of a month.
Example Suppose current date is 09/07/2013. Output should be 31/07/2013. What is SQL query for that?

Posted 8-Jul-13 23:49pm

Makarand Borawake

Add a Solution

3 solutions

Solution 2

If you want to find last day of month of any day specified use following script.

SQL

DECLARE @dtDate DATETIME
SET @dtDate = '09/07/2013'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

for more way, check out SQL SERVER – Find Last Day of Any Month – Current Previous Next[^]

Posted 9-Jul-13 0:04am

RaviRanjanKr

Solution 3

C#

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,'09/07/2013'))),DATEADD(mm,1,'09/07/2013')),101)

Posted 9-Jul-13 0:04am

maneavnash

Updated 9-Jul-13 0:09am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**Nirav Prabtani** · Accepted Answer · 2013-07-08T23:57:00

Solution 1

TRY THIS...:)

If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year

SQL

DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth

2007-08-31 23:59:59.000

SQL Get the last date of the month[^]

Posted 8-Jul-13 23:57pm

Nirav Prabtani

v2

Comments

Makarand Borawake 9-Jul-13 6:22am

Thanks...
I want to execute query for ms access database. Is it possible??

Nirav Prabtani 9-Jul-13 6:31am

try this link....:)
http://www.599cd.com/tips/access/last-day-of-month/

Makarand Borawake 9-Jul-13 6:38am

Thanks....
done some changes in above select statement and it works for access database!!

Nirav Prabtani 9-Jul-13 6:39am

ok...congrates...:)