[sql-server] 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.

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

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]

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)

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

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 .

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'

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

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

To check run:

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

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 

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);

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.

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