CODEWITHSUNDEEP
sql-serversql-server-2005datedatetime
prabu R
prabu R

Reputation: 2209

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.

Upvotes: 57

Views: 313135

Answers (15)

Md. Nazmul Alom
Md. Nazmul Alom

Reputation: 199

Please try

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

Upvotes: 1

boot-scootin
boot-scootin

Reputation: 12515

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

Upvotes: 1

Rav
Rav

Reputation: 31

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.

Upvotes: 0

Ricardo Roa
Ricardo Roa

Reputation: 173

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

Upvotes: -1

Ravi Sharma
Ravi Sharma

Reputation: 370

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

Upvotes: -1

John Christopher
John Christopher

Reputation: 37

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 .

Upvotes: 0

J Hightower
J Hightower

Reputation: 31

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'

Upvotes: 0

Saikh Rakif
Saikh Rakif

Reputation: 135

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]

Upvotes: -1

Deepak
Deepak

Reputation: 123

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

Upvotes: 0

Subramanian
Subramanian

Reputation: 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

Upvotes: -1

Andomar
Andomar

Reputation: 238086

SQL Server 2012 introduces the eomonth function:

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

Upvotes: 19

Aaron Lelevier
Aaron Lelevier

Reputation: 20780

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

To check run:

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

Upvotes: 4

MgSam
MgSam

Reputation: 12803

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)

Upvotes: 66

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

Upvotes: 9

Devart
Devart

Reputation: 121922

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

Upvotes: 86

Related Questions