SQL query to find last day of current month?

Question

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

Hi everyone I have a query to find the last day of current month, which is surely working well, but I am unable to understand it, because I have other similar requirements and have to change it accordingly.

Can somebody explain it to me.. Thanks in advance

Answer 1

Get the DateTime of Now

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

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

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

Add the difference to '1900-01-01' plus one extra month

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

Remove one second

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