SQL query to find last day of current month?

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIF开发者_StackOverflow中文版F(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

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

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

select EOMONTH(GETDATE())

From Microsoft tech net

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

SELECT DATEPART (DD,EOMONTH(GETDATE())) AS 'the last day of the Current month'

• Here DATEPART function is used to print the integer part of day.. if the the name of day is asked then we have to use DATENAME() function.

• In query, last day means end of the month so we used EOMONTH() function.

• GETDATE() is to represent current month.