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.
The one line answer
When I wrote this post the answer below was the only way to do it. SQL Server 2012 added a function that does the whole job, so if you are on 2012 or later, this is all you need.
SELECT EOMONTH(GETDATE()) AS LastDay_CurrentMonth;
EOMONTH takes a second argument that shifts the answer by a number of months, which covers previous and next in the same breath.
SELECT EOMONTH(GETDATE(), -1) AS LastDay_PreviousMonth,
EOMONTH(GETDATE(), 0) AS LastDay_CurrentMonth,
EOMONTH(GETDATE(), 1) AS LastDay_NextMonth;It works on any date, not just today, so the “any month” part of the question is covered too.
DECLARE @dtDate DATETIME = '2007-08-18'; SELECT EOMONTH(@dtDate) AS LastDay_AnyMonth;
February in a leap year, months with 30 days, months with 31, it gets them all right without you thinking about it.
A word about 23:59:59
The older script further down returns the last day with a time of 23:59:59.000 on it. EOMONTH returns a plain date instead, and that difference matters more than it looks.
If you use the old value in a range, anything that happened in the last second of the month is quietly left out. An order stamped 23:59:59.400 falls after 23:59:59.000, so it never appears in your report, and nobody notices until the monthly totals do not add up.
The safe habit is to stop using the end of the month at all and use the start of the next one instead.
SELECT * FROM Orders WHERE OrderDate >= '2007-08-01' AND OrderDate < '2007-09-01';
Greater than or equal to the first of the month, and strictly less than the first of the next. No time component to get wrong, no rows lost at midnight, and it works whatever the data type of the column.
Or get it from EOMONTH of the previous month plus one day, whichever reads better to you.
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS FirstDay_CurrentMonth;
The original method, for SQL Server 2008 R2 and earlier
If you are on an older version, or you are reading someone else’s script and want to know what it does, here is the method this post was written for. 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
In plain words, it counts the months since a fixed starting point, moves forward to the first of the next month, then steps back one second. Clever, and it worked for years, but EOMONTH says what it means and nobody has to decode it.
If you want the first day of the month as well, that is a question of its own and I have answered it in Query to Find First and Last Day of Current Month. There is a function version of this one in Script/Function to Find Last Day of Month. Use whichever method suits your requirement.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
289 Comments. Leave new
Thanks man, it was very usefull for me!
Written 8 years ago and saved me a lot of time today. Many thanks!
Thanks a lot I got my solution.
I wrote the following query.
select convert(datetime, cast(subs_to_yr as nvarchar) + right( (‘0’+LTRIM(RTRIM(subs_to_mth)) ),2) + ’01’) as c1 , subs_to_yr, subs_to_mth from MEM_DEP_ENTRY where subs_to_yr is not null and Subs_To_Mth > 0
Update MEM_DEP_ENTRY set Validdate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, convert(datetime, cast(subs_to_yr as nvarchar) + right( (‘0’+LTRIM(RTRIM(subs_to_mth)) ),2) + ’01’) )+1,0)) where validdate is null and subs_to_mth > 0 and Subs_To_Yr is not null
The simple method is select dateadd(month,datediff(month,0,’2015-09-10′),-1)
Hi All,
If we have only values coming in months like Jan, Feb , Mar etc.
How can I find Last date of every month w.r.t a month respectively
select DATEADD(s,-1,DATEADD(m,DATEDIFF(m,0,GETDATE()),0))
How to fetch data from data base from last three days(29-02-2016 to 3-03-2016, if second march is not having any data
Thank you! You give great tips…this one included!
I am glad you liked it @Michael
SELECT EOMONTH(GETDATE()) can also be used to get the last day of current date. :)
Oh yeah. nice trick, i must say.
Nice trik
I love this blog! Thank you Mr. Pinal
I’ve put together the below to try to explain how dates work. Everything stems from the fact that, in SQL terms, the beginning of time was 01-01-1900.
I add / subtract days rather than seconds as this gives a date without time so I can use ” date =” in my queries.
I’ve added the EOMONTH function which is only available in later versions of SQL.
Hope this helps someone.
DECLARE @TestDate DATETIME
SET @TestDate = ‘2016-11-15’
SELECT
‘The beginning of time’ = CONVERT(DATETIME,0),
‘Tomorrow’ = @TestDate + 1,
‘Months since time began plus one’ = DATEDIFF(m,0,@TestDate)+1, –=1403
‘First Day of Next Month with Months int’ = DATEADD(m,1403,0)
–(add 1403 months to the beginning of time)
SELECT
‘First Day of Next Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)+1,0),
‘Last Day of This Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)+1,0)-1,
‘First Day of Last Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)-1,0),
‘Last Day of Last Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate),0)-1,
‘Last Day of Next Month’ = DATEADD(mm, DATEDIFF(m,0,@TestDate)+2,0)-1
SELECT
‘First Day of Next Month’ = CONVERT(DATETIME,EOMONTH(@TestDate))+1,
‘Last Day of This Month’ = CONVERT(DATETIME,EOMONTH(@TestDate)),
‘First Day of Last Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,-2))+1,
‘First Day of This Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,-1))+1,
‘Last Day of Last Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,-1)),
‘Last Day of Next Month’ = CONVERT(DATETIME,EOMONTH(@TestDate,1))
@Spyder – Thanks for sharing it.
I am supplying @month and @year anyway to pull the last day of the month and year provided?
EOMONTH
Nice post Dave. Really helpful. Thanks.
thanks Hadi.
i have data firstdate = ‘2010/10/25’ and second = today(2017/09/04),
how to preview like 6 Years, 10 months and 9 days…?
example in excel we use
DATEDIF(H9;$P$2;”Y”)&” Years “&DATEDIF(H9;$P$2;”YM”)&” Months “&DATEDIF(H9;$P$2;”MD”)&” Days”
how to preview in sql server.. please help me… tq
Hello Breng qie,
Try this. If your start date and end date are columns, you will need to create this as a function.
DECLARE
@StartDate DATETIME = ‘2010-10-25’,
@EndDate DATETIME = ‘2017-09-04’,
@Years INT,
@Months INT,
@Days INT
IF MONTH(@StartDate) < MONTH(@EndDate)
SELECT
@Years = YEAR(@EndDate) – YEAR(@StartDate),
@Months = MONTH(@EndDate) – MONTH(@StartDate)
ELSE
SELECT
@Years = YEAR(@EndDate) – YEAR(@StartDate) – 1,
@Months = MONTH(@EndDate) – MONTH(@StartDate) + 11
IF DAY(@StartDate) < DAY(@EndDate)
SET @Months = @Months + 1
ELSE
SET @Startdate = DATEADD(DAY,1,@StartDate)
SELECT
@StartDate = DATEADD(YEAR,@Years,@StartDate),
@StartDate = DATEADD(MONTH,@Months,@StartDate),
@Days = DATEDIFF(DAY,@StartDate, @EndDate)
SELECT
CONVERT(VARCHAR(10),@Years) + ' Years, '
+ CONVERT(VARCHAR(2),@Months) + ' Months and '
+ CONVERT(VARCHAR(2),@Days) + ' Days'
Here’s the function. I’d be very interested if anyone has a more elegant way of doing this………?
CREATE FUNCTION fn_GetDateCount
(
@StartDate DATETIME = ‘2010-10-25’,
@EndDate DATETIME = ‘2017-09-04’
)
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE
@Years INT,
@Months INT,
@Days INT,
@Output VARCHAR(256)
IF MONTH(@StartDate) < MONTH(@EndDate)
SELECT
@Years = YEAR(@EndDate) – YEAR(@StartDate),
@Months = MONTH(@EndDate) – MONTH(@StartDate)
ELSE
SELECT
@Years = YEAR(@EndDate) – YEAR(@StartDate) – 1,
@Months = MONTH(@EndDate) – MONTH(@StartDate) + 11
IF DAY(@StartDate) < DAY(@EndDate)
SET @Months = @Months + 1
ELSE
SET @Startdate = DATEADD(DAY,1,@StartDate)
SELECT
@StartDate = DATEADD(YEAR,@Years,@StartDate),
@StartDate = DATEADD(MONTH,@Months,@StartDate),
@Days = DATEDIFF(DAY,@StartDate, @EndDate)
SELECT @Output =
CONVERT(VARCHAR(10),@Years) + ' Years, '
+ CONVERT(VARCHAR(2),@Months) + ' Months and '
+ CONVERT(VARCHAR(2),@Days) + ' Days'
RETURN @Output
END
–To USE: SELECT dbo.fn_GetDateCount ('2010-10-25', '2017-09-28')
— or with columns from a table SELECT dbo.fn_GetDateCount (StartDate,getdate()) FROM EMPS
Last Day of Any Month and Year calculation in SQL saved my day, I needed this column to use in DAX expression in Tabular model to get sales value of the last day MTD for any given month.
DECLARE @date DATETIME = ’12/1/2011′;
SELECT EOMONTH ( @date ) AS Result;
GO
Blog was written in year 2007 when EOMONTH was not there in SQL. It was introduced in SQL 2012, I think.
Thank you very much sir. useful since stuck on sql 2008 for now
hello sir. thank you for the solution . But kindly describe the formula and logic for the same. as a fresher can understand that also.
;WITH monthstart
AS (SELECT CONVERT (DATE, Dateadd(year, Datediff(year, 0, Getdate()), 0))
AS
[BeginDate]
UNION ALL
SELECT Dateadd(month, 1, [begindate])
FROM monthstart
WHERE [begindate] < CONVERT (DATE, Dateadd(month, -1, Dateadd(year,
Datediff(year, 0,
Getdate()) + 1
, -1)))),
monthend
AS (SELECT Eomonth(CONVERT (DATE, Dateadd(year, Datediff(year, 0, Getdate()
), 0)))
AS
[LastDate]
UNION ALL
SELECT Eomonth(Dateadd(month, 1, [lastdate]))
FROM monthend
WHERE [lastdate] < CONVERT (DATE, Dateadd(year, Datediff(year, 0,
Getdate()
) +
1, -1)))
SELECT Month([begindate]) AS [Month],
[begindate],
[lastdate]
FROM monthstart
JOIN monthend
ON Month(begindate) = Month(lastdate)
OMG a post written 13 years ago is still so helpful! Thanks Pinal!
Great to hear that.