SQL Query to find the last day of the month

Question

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.

Answer 1

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

Answer 2

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

To check run:

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

Answer 3

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

Answer 4

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

Answer 5

SQL Server 2012 introduces the eomonth function:

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

Answer 6

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"

1. 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"

1. 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"

1. 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...

I hope this article will help.