Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.
I have answered this question twice before here:
SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Query to Find First and Last Day of Current Month
Today, we will see the same solution again. Please use the method you find appropriate to your requirement.
Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000
LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000
LastDay_NextMonth
———————–
2007-09-30 23:59:59.000
If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year
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
Reference : Pinal Dave (https://blog.sqlauthority.com)
289 Comments. Leave new
how to find the day of month ……..
only enter month
ex:month=04
then
find the last date of this month…..using vb.net
If you want to pass only month, what about year?
Which year do you want to combine?
You’re the man!
Whenever I have a question google sends me to you…
And you always have an answer.
Kudos to you!
I need to find the last day of the previous month based on a date entered by a user.
Here you go
select dateadd(month,datediff(month,0,’2015-09-10′),-1)
Also, the date the user will be entering will always be the last day of some month.
Thanks,
It does not matter.
select dateadd(month,datediff(month,0,’2015-09-30′),-1)
@Donna
You can subtract the amount of days in the passed date. That will give the last day of the prior month.
DECLARE @A DATETIME
SET @A = ’04/30/2009′
select DATEADD(d, -DATEPART(d, @A), @A)
in short
DECLARE @A DATETIME
SET @A = ’04/30/2009′
select DATEADD(day, -DAY(@A), @A)
Please help. In my stored procede, I want to assign the return value of ‘SELECT DATEADD(dd, -Day(GetDate()), GetDate())’ to a variable, e.g.
SET @PreMonthEnd = SELECT DATEADD(dd, -Day(GetDate()), GetDate())
What is the correct statement I should write? Thanks a lot.
SET @PreMonthEnd = DATEADD(dd, -Day(GetDate()), GetDate())
All above statements seems complicated to find last date of previous month… how abt this
select dateadd(d,-day(getdate()),getdate())
Thanks ,sunny
It’s very useful for me …………thanks a lot
thanks a lot man
u r awesome
thank u
Most beneficial,
Thanks
what will select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0))) return?
thanks
You need to test it yourself
It is currently giving error
hi there
I need to have a bimonthly report i.e. once with 01/01/2009 to 15/01/2009
and then 16/01/2009 to 31/01/2009
i want to know how can i get starting and ending dates like 1 and 15 of current month if the current date is > 15 and 16 to 30/31 of the previous month if current date is < 15
thanks a lot in advance.
zee
@zee
WHERE date-COLUMN >= CASE
WHEN DATEPART(dd, GETDATE()) < 16 THEN DATEADD(DD, 15, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) – 1, 0))
ELSE DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
END
AND date-COLUMN <
CASE
WHEN DATEPART(dd, GETDATE()) < 16 THEN DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
ELSE DATEADD(DD, 15, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) – 1, 0))
END
Pinal, your script worked perfectly. Thank you very much for sharing this!
Thank you very much for sharing this!
how to find first and last day of any month in sql?
select dateadd(month,datediff(month,0,getdate()),-1)
Excellent !
the best answer I found !
thanks
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()),1) -1
will return last day of current month (looks shorter)
Correction
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0) -1
will return last day of current month (looks shorter)
How do I get the Date that is 3 months prior than today? So, three months ago from today.
PN
Actually, I discovered it: dateadd(month, -3, GetDate())
This will give date and time.
I am working on formatting to get only the date.
Thank you.