Find Last Day of Any Month (SQL Server)

a) Find the last day of the previous month, current month and next month.

• Last Day of Previous Month

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

• Last Day of Current Month

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

• Last Day of Next Month

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

b). Last day of any day specified.

• Last Day of Any Month and Year

DECLARE @dtDate DATETIMESET @dtDate = '8/18/2007'SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))