How to get last date of month SQL Server 2008

sql sql-server sql-server-2008

To get the last day you can do this:

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2014-08-12')+1,0))

Adding to your function:

select @New_date = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0))

Source:

sql sql-server sql-server-2008

For those who are using SQL Server 2012,EOMONTH function could be an alternative.

DECLARE @date DATETIME = '12/1/2011';SELECT EOMONTH ( @date ) AS Result;GO

sql sql-server sql-server-2008

Go to the first day of the month. Add one month. Then subtract one day. Or, in your case, one second:

select @New_date = dateadd(second, -1, dateadd(month, 1, dateadd(day, -(DAY(@Date) + 1)) ) )

You should use convert() if you want this as a string. I would instead suggest that the function return a datetime.

CodeHunter