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
Thnks sir providing code for getting last date of previous month in sql server.
I need a solution …
Here are the details….
Step a) I will select a month and year(eg. January 2012) from UI.
Step b) It should return four months back data (only month number).
for this purpose i am trying to use datediff(m,,) function, this is perfectly working for current year( from May to December month).
but when i am selecting January month it is returning current years September month but i need to get last year’s September month. Same as for February, March, April month.
Please help.
for Last Date of previous month
select DATEADD(D,-( DATEPART(D,GETDATE())),GETDATE())
select GETDATE()- DAY(GETDATE())
or
select EOMONTH(GETDATE(),-1) /* in SQL 2012*/
will give same result
You can find lot of such methods at the bottom section of
–Alternate method
DECLARE @MyDate DATETIME
SET @MyDate = ’26-Jul-2013′
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,-1,@MyDate))
SELECT DATEADD(d, -1*day(@MyDate), @MyDate)
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,1,@MyDate))
–Alter Nate Method
DECLARE @MyDate DATETIME
SET @MyDate = ’26-Jul-2013′
— Previous Month
SELECT DATEADD(d, -1*day(@MyDate), @MyDate)
— Given Month
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,1,@MyDate))
— Next Month
SELECT DATEADD(d, -1*day(@MyDate), DATEADD(mm,2,@MyDate))
Thank you very much for your post !
I want exact month end date so
declare @date datetime
set @date=’31-jan-2012′
while @date<='31-jul-2012'
Begin
print @date
set @date= DATEADD(M,1,@date)
End
but i'm getting result like
Jan 31 2012 12:00AM
Feb 29 2012 12:00AM
Mar 29 2012 12:00AM
Apr 29 2012 12:00AM
May 29 2012 12:00AM
Jun 29 2012 12:00AM
Jul 29 2012 12:00AM
Please help me to get exact month end date
declare @date datetime
set @date=’20120101′
select dateadd(month,datediff(month,0,dates)+1,-1) as last_day from
(
select dateadd(month,number,@date) as dates from master..spt_values
where type=’p’ and number between 0 and 6
) as t
Just Create this Stored Procedure in a Data Base
And call it
============================
USE [TestDataBase]
GO
/****** Object: UserDefinedFunction [dbo].[LAST_DAY] Script Date: 11/08/2012 00:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Luis R. Vela Morales
– Create date: 01/11/2012
– Description: De una fecha dada, retorna la
– fecha con el ultimo dia del mes,
– esto decrementando, sin afectar o
– incrementando en ‘n’ meses
– ( -n, 0, n).
– =============================================
CREATE FUNCTION [dbo].[LAST_DAY]
(
– Add the parameters for the function here
@Fecha datetime,
@Intervalo int
)
RETURNS datetime
AS
BEGIN
– Declare the return variable here
DECLARE @Result datetime
– Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(day,-day(DATEADD(mm, 1+@Intervalo,@Fecha)),DATEADD(mm, 1+@Intervalo,@Fecha))
– Return the result of the function
RETURN @Result
END
Hi Dear,
Very Important article of SQL Developers
Thank you.
Last Year last month (if today is 30-Apr-2013, it should reflect 30-Apr-2012)
SELECT EOMONTH(SYSDATETIME()) AS end_of_current_month;
This will work from version 2012 onwards
Can i get the only month and year of ther previous month in the FORMAT JUN-13
using the query,
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-2,0))
LastDay_PreviousMonth,
How can I get the date in yyyymmdd format ?
SELECT CONVERT(VARCHAR,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-2,0)),112)
Hi all, SQL Server 2012 has new Function EOMONTH and makes life easier:
DECLARE @mydate DATETIME= GETDATE()
SELECT EOMONTH(@mydate,1) AS [Last Day Of Next Month];
SELECT EOMONTH(@mydate) AS [Last Day Of Current Month];
SELECT EOMONTH(@mydate,-1) AS [Last Day Of Previous Month];
SELECT DATEADD(dd,1,EOMONTH(@mydate)) AS [First Day Of Next Month];
SELECT DATEADD(dd,1,EOMONTH(@mydate,-1)) AS [First Day Of Current Month];
another way but slightly modified in sql 2012. I think it might be repetitive
select dateadd(mm,0,getdate())-day(getdate())- to get previous month last date.
select dateadd(mm,1,getdate())-day(getdate())-to get current month last date.
select dateadd(mm,2,getdate())-day(getdate())-to get next month last date.
depending upon what we need we can increase the second arguement.
Great site. I think my request is simple, but I could not find it. My file is currently daily, and I want to reduce it to only the last day of all the months that the database provides. Similarly, want the last day, period, of the given item. Some items are current, and some expired several years ago.
Thanks,
Stephen
I find the way to calculate the same.
DECLARE @PARAMETER DATETIME,
@MONTH VARCHAR(20),
@YEAR VARCHAR(20)
SELECT @MONTH = ‘FEBRUARY’,@YEAR = ‘2012’
SELECT @PARAMETER = @MONTH +’ ‘+@YEAR
DECLARE @DATE DATETIME
SET @DATE = SUBSTRING(REPLACE(REPLACE(CONVERT(VARCHAR(19), @PARAMETER, 126),’-‘,”),’:’,”),1,8)
SELECT DATEADD(DAY,-1,DATEADD(MM,1,@DATE)) [LAST DAY OF MONTH],@DATE [FIRST DAY OF MONTH]
Please solved issues – start date and end date should be different why….
SELECT DATEADD(MONTH,-1,DATEADD(MONTH,1378,0))
SELECT DATEADD(SECOND,-1,DATEADD(MONTH,1378,0))
Great tips. Thx a lot.
@Guazzelli, Glad that you liked it.
Great post, I would like just improve your function to get Last Day of Current Month
you can use some thing look like this: (DATEADD(MONTH, 1, @ano_mes + ’01’) – 1)
that’s great System!
Sir Please Help Me
function that will return all the previous month names of a particular year ,if date is passed to that function as an input parameter.
For eg if date is passed as 06/08/2009 then output should be
Jan
Feb
March April
May
June
July
Aug
thanx in advance
Prashant – I replied for another comment with same question.
It would be better to create table with month names and use that to get the desired output.
Thanks for great blog.
How do I calculate for last day of previous 6 months?
EOMONTH is the function.