title: How to get last date of month SQL Server 2008
body: <p>I created a function "ufngetFirstDateOfMonth" and "ufngetLastDateOfMonth"  stored in Microsoft SQL Server 2008. My purpose is to send some date into the function and it will return the first date of month with '00:00:00' or the last date of month with '23:59:59'.</p>
  
  <p>I call the function like this:</p>
  
  <pre><code>exec ufngetLastDateOfMonth('2014-10-15')  
  </code></pre>
  
  <p>and normally it returns '2014-10-31 23:59:59'<br />
  but when I send the last date of months that have 31 days (august, january,...):</p>
  
  <pre><code>exec ufngetLastDateOfMonth('2014-10-31')
  </code></pre>
  
  <p>it return '2014-10-30 23:59:59' whick is not correct  Actally, it should be '2014-10-31 23:59:59'</p>
  
  <p>Something goes wrong here... </p>
  
  <p>This is my function:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[ufnLastDateOfMonth](@Date date)
  RETURNS varchar(50)
  
  AS
  BEGIN
  
  DECLARE @New_Date varchar(50)
  
  select @New_date = cast(dateadd(dd,-(DAY(@Date )),DATEADD(mm,1,@Date ))as varchar(50)) + ' 23:59:59'
  
  RETURN @New_Date
  
  END
  </code></pre>