Click here to Skip to main content
15,035,685 members
Please Sign up or sign in to vote.
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

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[^]
   
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...:)
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[^]
   
C#
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,'09/07/2013'))),DATEADD(mm,1,'09/07/2013')),101)
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900