SQL query to find the last day of the current month?

Question

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) LastDay_CurrentMonth 

Hi everyone, I have a request to find the last day of the current month, which certainly works well, but I can’t understand it, because I have other similar requirements and you need to change it accordingly.

Can someone explain this to me .. Thanks in advance

Answer 1

Get DateTime Now

 GETDATE() -- 2011-09-15 13:45:00.923 

Calculate the difference in the month from '1900-01-01'

 DATEDIFF(m, 0, GETDATE()) -- 1340 

Add the difference to "1900-01-01" plus one additional month

 DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0) -- 2011-10-01 00:00:00.000 

Delete one second

 DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0)) -- 2011-09-30 23:59:59.000 

Answer 2

This will give you the last day of the current month, but ignores the time.

select EOMONTH (GETDATE ())

From Microsoft Technical Network

Answer 3

 CREATE FUNCTION EOMONTH ( @date datetime, @months int ) RETURNS datetime AS BEGIN declare @eom datetime declare @d datetime set @d = dateadd(MONTH, @months, @date) select @eom = dateadd(SECOND,-1,DATEADD(MONTH,datediff(MONTH,0,@d)+1,0)) RETURN @eom END GO