Get end of the month in mssql (2008 R2 / 2012 )

This is a simple workout to find the end day of the month for selected date in SQL server. For SQL server 2012, we have a built-in function to perform this task but for 2008, we need to write the logic manually. Here are the sql query samples to find out the end day of the selected month in mssql.

let's assume @dt is your declared DateTime

DECLARE @dt DATETIME = GETDATE()

In SQL 2012, we have an inbuilt function which will give directly the end of the month.

SELECT DAY(

EOMONTH(

@dt   -------- date to get end of the month

,0   -------- months to add ( optional )

)

)

-- In Short :

SELECT DAY(EOMONTH(@dt,0))

But in sql 2008 R2, we do not have this function, hence we need a workout. 

SELECT DAY(   --------------------------------------- returns the number of days of that month

DATEADD(d,-1, --------------------------- subtracts one day from the diff date

DATEADD( ------------------------ adds 1 month to the difference month: return type is DateTime                    

m

,DATEDIFF(M,0,@dt)+1  --- gets difference in months from the very beginning 

,0

)

)

   ) 

           

-- In Short :

SELECT DAY(DATEADD(d,-1,DATEADD(m,DATEDIFF(M,0,@dt)+1,0)))

For end of month in javascript check the link: http://iamfixed.blogspot.de/2017/12/get-end-day-of-month-jquery-javascript.html