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
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.