53

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.

prabu R
  • 2,099
  • 12
  • 32
  • 41

15 Answers15

85

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
Devart
  • 119,203
  • 23
  • 166
  • 186
  • 3
    Just remember that if you're using this in a between statement to get (for example) all records for a given month, this will exclude records from the last day. You may need to use the first day of the next month instead. – SteveCav Mar 16 '15 at 00:47
61

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)
MgSam
  • 12,139
  • 19
  • 64
  • 95
17

SQL Server 2012 introduces the eomonth function:

select eomonth('2013-05-31 00:00:00:000')
-->
2013-05-31
Andomar
  • 232,371
  • 49
  • 380
  • 404
9
declare @date datetime;
set @date = getdate(); -- or some date
select dateadd(month,1+datediff(month,0,@date),-1);
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
4
dateadd(month,1+datediff(month,0,getdate()),-1)

To check run:

print dateadd(month,1+datediff(month,0,@date),-1)
Aaron Lelevier
  • 19,850
  • 11
  • 76
  • 111
1

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
blacksite
  • 12,086
  • 10
  • 64
  • 109
1

Please try

SELECT CONVERT(DATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) AS lastDayOfMonth
Md. Nazmul Nadim
  • 207
  • 2
  • 5
0

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.
MasterJoe
  • 2,103
  • 5
  • 32
  • 58
Rav
  • 31
  • 1
0
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 .

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
John Christopher
  • 37
  • 1
  • 8
0
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
Deepak
  • 103
  • 2
  • 3
  • 11
0

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'

SomeDutchGuy
  • 2,249
  • 4
  • 16
  • 42
J Hightower
  • 31
  • 3
-1
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
Subramanian
  • 1
-1
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]
Saikh Rakif
  • 135
  • 3
-1

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

Ravi Sharma
  • 362
  • 1
  • 5
-1

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
Ricardo Roa
  • 177
  • 3
  • 12