Getting the last day of an already existing date in SQL Server Management Studio

Question

I have a table with a list of clients, with a column that shows the date their accounts were created. I want to make the last day of this date. I tried using the EOMONTH function, but it does not work.

For instance, if client 1 came on January 6th, and client 2 came on February 6th of this year, I want it to show 31-01-2018 and 28-02-2018.

Any ideas?

Thanks.

And how does `eomonth()` not work? That is exactly what the function does. — Gordon Linoff, Feb 14 '18 at 12:23
Have a look [here](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/490579bc-966a-4f16-8ad7-69c55dbe3afe/emonth-equivalent-function-in-sql-2008), several options are mentioned. — HoneyBadger, Feb 14 '18 at 12:44

Sandip - Frontend Developer · Answer 1 · 2018-02-14T12:41:11.193

Gordon is right, I have tried EOMONTH for your given data and it returns perfect value look at below, in second parameter you have to pass value as per requirement if you pass value as 1 it will give you last date of next month i.e Jan+1=Feb

 DECLARE @myDate Datetime='06-Jan-2018'
 SELECT EOMONTH(@myDate,0) AS MyDate

If your version not support the EOMONTH function then try below query:

 SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@myDate)+1,0)) AS DATE)

Output:

Getting the last day of an already existing date in SQL Server Management Studio

1 Answers1