DateAdd function returns wrong result

• Jim ONeill

  SSC Veteran

  Points: 238

  More actions

  September 28, 2011 at 2:25 pm

  #257995

  When I run the following query in SS2008 to get days around the end of the month, I get the same results for Date_7 and Date_8. Any guess as to why?

  Select

  getdate() As Date_1

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),+3) As Date_3

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),+2) As Date_4

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),+1) As Date_5

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),-0) As Date_6

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),-1) As Date_7

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),-2) As Date_8

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),-3) As Date_9

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  September 28, 2011 at 2:40 pm

  #1388093

  Try this instead

  DECLARE @ThisDate DATETIME = GETDATE()

  Select

  @ThisDate As Date_1

  ,dateadd(m,datediff(m,0,dateadd(m,1,@ThisDate)),0)+3 As Date_3

  ,dateadd(m,datediff(m,0,dateadd(m,1,@ThisDate)),0)+2 As Date_4

  ,dateadd(m,datediff(m,0,dateadd(m,1,@ThisDate)),0)+1 As Date_5

  ,dateadd(m,datediff(m,0,dateadd(m,1,@ThisDate)),0) As Date_6

  ,dateadd(m,datediff(m,0,dateadd(m,1,@ThisDate)),0)-1 As Date_7

  ,dateadd(m,datediff(m,0,dateadd(m,1,@ThisDate)),0)-2 As Date_8

  ,dateadd(m,datediff(m,0,dateadd(m,1,@ThisDate)),0)-3 As Date_9

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Lowell

  SSC Guru

  Points: 323518

  More actions

  September 28, 2011 at 2:43 pm

  #1388099

  Jim i think it's because you mixed up the order of the parameters.

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),+3) As Date_3

  the "+3" in the above is a DATE...not the number of days to add. it's actually 1900-01-04 00:00:00.000(select cast(3 as datetime))

  and you are adding like 1341 months to it.

  DATEADD (datepart , number , date )

  the difference is subtle...i think this is what you want:

  Select

  getdate() As Date_1

  ,dateadd(m,+3, datediff(m,0,dateadd(m,1,getdate()))) As Date_3

  ,dateadd(m,+2, datediff(m,0,dateadd(m,1,getdate()))) As Date_4

  ,dateadd(m,+1, datediff(m,0,dateadd(m,1,getdate()))) As Date_5

  ,dateadd(m,-0, datediff(m,0,dateadd(m,1,getdate()))) As Date_6

  ,dateadd(m,-1, datediff(m,0,dateadd(m,1,getdate()))) As Date_7

  ,dateadd(m,-2, datediff(m,0,dateadd(m,1,getdate()))) As Date_8

  ,dateadd(m,-3, datediff(m,0,dateadd(m,1,getdate()))) As Date_9

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  September 28, 2011 at 2:46 pm

  #1388101

  Lowell (9/28/2011)

  ---

  Jim i think it's because you mixed up the order of the parameters.

  ,dateadd(m,datediff(m,0,dateadd(m,1,getdate())),+3) As Date_3

  the "+3" in the above is a DATE...not the number of days to add. it's actually 1900-01-04 00:00:00.000(select cast(3 as datetime))

  and you are adding like 1341 months to it.

  DATEADD (datepart , number , date )

  the difference is subtle...i think this is what you want:

  Select

  getdate() As Date_1

  ,dateadd(m,+3, datediff(m,0,dateadd(m,1,getdate()))) As Date_3

  ,dateadd(m,+2, datediff(m,0,dateadd(m,1,getdate()))) As Date_4

  ,dateadd(m,+1, datediff(m,0,dateadd(m,1,getdate()))) As Date_5

  ,dateadd(m,-0, datediff(m,0,dateadd(m,1,getdate()))) As Date_6

  ,dateadd(m,-1, datediff(m,0,dateadd(m,1,getdate()))) As Date_7

  ,dateadd(m,-2, datediff(m,0,dateadd(m,1,getdate()))) As Date_8

  ,dateadd(m,-3, datediff(m,0,dateadd(m,1,getdate()))) As Date_9

  You might want to check the dates in your results (Dec, Nov, Oct etc of 1903);-)

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Yuri55

  SSCrazy Eights

  Points: 8449

  More actions

  September 28, 2011 at 2:57 pm

  #1388109

  Jim,

  Eventually these 2 dates calculation is the same as (I supressed the rest):

  select

  dateadd(m, 9, '2010-12-31 00:00:00.000'), --7

  dateadd(m, 9, '2010-12-30 00:00:00.000') --8

  Result is the same- probably because in September we have 30 days

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  September 28, 2011 at 4:34 pm

  #1388143

  It's short months for sure.

  If you add two months to 30th dec 1899 you get 28th feb 1900.

  If you add two months to 31st dec 1899 you get 28th feb 1900.

  There are many of these, whenever adding a month hits an invalid date.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]