Bug in SQL Server 2005 ? - DATEADD function

Bug in SQL Server 2005 ? - DATEADD function

• psychogeek

  Hi Imtiaz,

   

  If the starting date is known to be a last day of the month and the last day of the next month is needed, then try:

   

  SELECT DATEADD(day, -1, DATEADD(month, 1, DATEADD(day, 1, '20060228')))

  
  --
  Hugo Kornelis, SQL Server MVP

   

  <Imtiaz@discussions.microsoft.com> schreef in bericht news:38fd1920-3792-42d9-9689-bfafc8f3c0ba@discussions.microsoft.com...

  Hi

  When I execute the following , the expected result is 03/31/2006. However the result that you get is 2006-03-28 00:00:00.000

  Select DATEADD(MONTH, 1, '02/28/2006')

  Is this something how the dateadd is supposed to behave or is this a bug in SQL Server 2005.

  Regards

  Imtiaz

  
  

• WallyG

  If your base date is not the last of a given month, something like this will work:
  
  DECLARE @mydate DATETIME
  SET @mydate = '20060201'
  SELECT DATEADD(MONTH,DATEDIFF(MONTH,30,@mydate)+1,30)
  --
  Frank Kalis
  Microsoft SQL Server MVP
  http://www.insidesql.de
  
  
  

• schrempfi

  Well if you use the Oracle Add_months function this results in 31-Mar-2006.

  Regards

  Imtiaz

  
  

• elkestra

  Hi Imtiaz,

  I can't actually see anything wrong here. Why would the expected result be 31 March 2006 That is not one month in the future, that is one month and x days.

  What you are trying for is a "End of Month" function...

  Cheers

  Rob

  
  
• Bug in SQL Server 2005 ? - DATEADD function