Find Last Day of Any Month – Current Previous Next

Following script demonstrates the script to find last day of previous, current and next month.

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

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

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2010-05-31 23:59:59.000

LastDay_CurrentMonth
———————–
2007-06-30 23:59:59.000

LastDay_NextMonth
———————–
2007-07-31 23:59:59.000 

If you want to find last day of month of any day specified use following script.

DECLARE @dtDate DATETIME
SET @dtDate = '06/16/2010'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–
2010-06-30 23:59:59.000

|