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
This may sound like a strange question but how do you find the first day of month say 3 months before Last Day of Previous Month(as defined by “SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) “)
So my script looks something like this
and startdte >= “”Here is where I needthe first day of the month three months prior to the last day of the previous month””
and startdte <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
SELECT dateadd(month,datediff(month,0,getdate())-4,0)
I need to amend the query to take weekends into consideration – more precisely, I would like to get the last day in a month that is not a weekend. Is it possible to write such an sql query?
Cheers.
Try this logic
select last_date+case when datepart(weekday,last_date) =1 then -2 when datepart(weekday,last_date) =7 then -1 else 0 end from
(
select dateadd(month,datediff(month,0,’20090120′)+1,-1) as last_date
) as t
Thans a ton! As you already know, it works (like a charm). I would not have been able to solve this on my own =) Well done!
Great solution! Thanks!
Hello Again,
Is it possible to test if the selected date is a monday??
Cheers.
use this
select DATENAME(dw,getdate())
Please help me with this, i have a table with the follwoing fields:
Date
Duration (in Months, eg 6 months, 12 month or 18 months)
StartDate
EndDate
from StartDate, I want to find out last date of the month as EndDate, which is determined by Duration(in Months).
Regards.
This is the best way to find last day of the month in sql server
SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0,GETDATE())+1,-1)
Also note that you dont need to use distinct in this case
it is very nice artical
i have the following code to get the records from today to two months in the past (from 1st day of that month), how can i add condtion to get the months from previous year e.g. december 2010 when i run the query in 2011
dt_updated > (select DATEADD(MONTH,-2,DATEADD(DAY,1-DAY(CURRENT_TIMESTAMP),CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))))
select dateadd(year,datediff(year,0,getdate())+1,0)-1
thans Madhivanan,
I added the suggested line of code and run the query now I’m not getting any records.
dt_updated > (select DATEADD(MONTH,-2,DATEADD(DAY,1-DAY(CURRENT_TIMESTAMP),CONVERT(CHAR(8),CURRENT_TIMESTAMP,112))))
and
dt_updated > (select dateadd(year,datediff(year,0,getdate())+1,0)-1)
perhaps I need to combine the two into one. any hint?
I am using the first of previous month and last of previous month….
WHERE prof.profdate between DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
I keep getting some results with dates for 12/01/2010…I am only looking for the dates between 11/01/2010 and 11/30/2010…
The prof.profdate column is smalldatetime…Does anyone see what I am doing wrong?
where
prof.profdate>=dateadd(month,datediff(month,0,getdate())-1,0) and
prof.profdate<dateadd(month,datediff(month,0,getdate()),0)
I didnt understand anything
explain me the logic fully please
Tell us what you didn’t understand
How will find the first day of current previous month?
Your posts are always very helpful and teach me so much. Thank you!
thank u very much.u save me.
Just want to say “thank you”, using your site alot
ok, so I am stuck and you all seem to know what you are taling about! (and you are going to think this query is really simple!)
I have this
(dateadd(d,-1,getdate()))
and I have added it into the SQL function in Excel. When it is refreshed it changes the data to the time that it currently is.
I want this to show data from the previous day midnight to midnight.
Can anyone explain in plain english how I do this please?
sarah
last date of current month
select DATEADD(m,1,GETDATE() -DAY(getdate()))
last date of next month
select DATEADD(m,2,GETDATE() -DAY(getdate()))
last date of previous month
select DATEADD(m,0,GETDATE() -DAY(getdate()))
I am sorry if someone has already suggested it
More informations
I am sorry if i am wrong but this gives wrong result if you pass 01 march
declare @d smalldatetime
set @d=’2011-03-01′
–last date of current month
select DATEADD(m,1,@d -DAY(@d))
–last date of next month
select DATEADD(m,2,@d -DAY(@d))
–last date of previous month
select DATEADD(m,0,@d -DAY(@d))
the following gives right on my pc..
declare @d smalldatetime
set @d=’2011-03-01′
–last date of previous month
select DATEADD(m,0,@d -DAY(@d)+1)-1
–last date of current month
select DATEADD(m,1,@d -DAY(@d)+1)-1
–last date of next month
select DATEADD(m,2,@d -DAY(@d)+1)-1
You can get last days of all months with the below code:
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+number-1,0))from master..spt_values where type=’P’
and number>=0 and number<12
Dear all,
should the following code bring the Last day of Previous Month? thanks!
DATEADD(s, -86400, DATEADD(mm, DATEDIFF(m, 0, GETDATE()),0)))
Dear all,
How to get all Sunday’s of current month ?
Thanks,
Mahesh T.
use it select DATENAME(dw,getdate())
or
select DATENAME(weekday,getdate())
Here is the code
select date from
(
select dateadd(day,number,dateadd(month,datediff(month,-1,getdate())-1,0)) as date from master..spt_values
where type=’p’ and number between 0 and day(dateadd(month,datediff(month,-1,getdate())+1,-1))-1
) as t
where datename(weekday,date)=’sunday’
Thanks. Now I need help fixing this hole in the wall from bagging my head. Great use of functions!