SQL Query to find the last day of the month

I need to find the last day of a month in the following format:

"2013-05-31 00:00:00:000"

Anybody please help out.

Try this one -

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth] 
(
	@Date DATETIME
)
RETURNS DATETIME
AS
BEGIN

	RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))

END

Query:

DECLARE @date DATETIME
SELECT @date = '2013-05-31 15:04:10.027'

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))

Output:

-----------------------
2013-05-31 00:00:00.000

I know this question was for SQL Server 2005, but I thought I'd mention- as of SQL 2012, there now is an EOMONTH() function that gets the last day of the month. To get it in the format specified by the original asker you'd have to cast to a datetime.

SELECT CAST(eomonth(GETDATE()) AS datetime)

SQL Server 2012 introduces the eomonth function:

select eomonth('2013-05-31 00:00:00:000')
-->
2013-05-31

declare @date datetime;
set @date = getdate(); -- or some date
select dateadd(month,1+datediff(month,0,@date),-1);

dateadd(month,1+datediff(month,0,getdate()),-1)

To check run:

print dateadd(month,1+datediff(month,0,@date),-1)

Calculate the last date of the month is quite simple calculation -

1 - Find the total months count till today's date using DATEDIFF function -

Select DATEDIFF(MM,0,GETDATE())

Output - 1374, If getdate() output is "2014-07-23 19:33:46.850"

2 -Increment by 1 into total months count -

Select DATEDIFF(MM,0,GETDATE())+1

Output - 1375, If getdate() output is "2014-07-23 19:33:46.850"

3 - Get the first date of next month -

Select DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

Output - '2014-08-01 00:00:00.000', If getdate() output is "2014-07-23 19:33:46.850"

4 - Subtract by -1 into the first date of next month, which will return last date of the current month -

Select DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0))

Output - '2014-07-31 00:00:00.000', If getdate() output is "2014-07-23 19:33:46.850"

In the same manner of calculation we can achieve the -

1. Last date of next month
2. Last date of the previous month and so on.

Select DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))

This works great in T-sql ..

Replace the GETDATE() of the query with your column name .

Declare @GivenDate datetime 
Declare @ResultDate datetime 
DEclare @EOMDate datetime 
Declare @Day int 
set @GivenDate=getdate() 
set @GivenDate= (dateadd(mm,1,@GivenDate)) 
set @Day =day(@GivenDate) 
set @ResultDate=dateadd(dd,-@Day+1,@GivenDate) 
select @EOMDate =dateadd(dd,-1 ,@ResultDate) 
select @EOMDate 

Just a different version of adding a month and subtracting a day for creating reports:

ex: StartofMonth is '2019-10-01'

dateadd(day,-1,dateadd(month,1,StartofMonth))

EndOfMonth will become '2019-10-31'

In Snowflake (and likely other SQL engines), you can use LAST_DAY.

select to_date('2015-05-08T23:39:20.123-07:00') as "DATE",
       last_day("DATE", 'MONTH') as "LAST DAY OF MONTH";

Which returns:

DATE         LAST DAY OF MONTH
2015-05-08          2015-05-31

Please try

SELECT CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) AS lastDayOfMonth

TO FIND 1ST and Last day of the Previous, Current and Next Month in Oracle SQL
-----------------------------------------------------------------------------
SELECT 
SYSDATE,
LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 FDPM,
LAST_DAY(ADD_MONTHS(SYSDATE,-1)) LDPM,
LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1 FDCM,
LAST_DAY(SYSDATE)LDCM,
LAST_DAY(SYSDATE)+1 FDNM,
LAST_DAY(LAST_DAY(SYSDATE)+1) LDNM
FROM DUAL

declare @date date=getdate()
declare @st_date date,@end_dt date
set @st_date=convert(varchar(5),year(@date))+'-'+convert(varchar(5),month(@date))+'-01'
set @end_dt=DATEADD(day,-1, DATEADD(month,1,@st_date))
---------**************--------------
select @st_date as [START DATE],@end_dt AS [END DATE]

select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-0, -1) LastDate

An excelent approach by me. Regards

DECLARE @MAXDATE INT=(SELECT MAX(DATEPART(YEAR,ORDERDATE)) FROM Orders)
DECLARE @MINDATE INT=(SELECT MIN(DATEPART(YEAR,ORDERDATE)) FROM Orders)
DECLARE @HORA INT=(SELECT MIN( DATEPART(HOUR,ORDERDATE)) FROM ORDERS)
DECLARE @DIA INT = 28

SELECT Employees.EmployeeID , Orders. OrderID , OrderDate  FROM Employees
INNER JOIN Orders 
ON Employees.EmployeeID = Orders.EmployeeID
Where  (DATEPART(YEAR,ORDERDATE)) >=@mindate and  (DATEPART(YEAR,ORDERDATE))<= @maxdate
and DATEPART(HOUR,ORDERDATE)=@HORA   and DATEPART(DAY,ORDERDATE) IN (30,31) OR DATEADD(DAY,0,DATEPART(DAY,ORDERDATE))=28 AND
DATEADD(MONTH,0,DATEPART(MONTH,ORDERDATE))=2
ORDER BY 1 ASC