Question on DATEADD

• Arthur.Lorenzini

  SSCrazy

  Points: 2312

  More actions

  November 10, 2008 at 10:09 am

  #73255

  I have this AND clause:

  AND c.DateCreated BETWEEN DATEADD(YEAR, -5, GETDATE())

  Is this correct? What I am trying to get to is any records from today to 5 years ago.

  Warm Regards,

  Arthur Lorenzini
  Sioux Falls, SD

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 10, 2008 at 10:17 am

  #896409

  Arthur.Lorenzini (11/10/2008)

  ---

  I have this AND clause:

  AND c.DateCreated BETWEEN DATEADD(YEAR, -5, GETDATE())

  Is this correct? What I am trying to get to is any records from today to 5 years ago.

  No, you are missing part of the between and you'll lose records because of the time. I'd change it to this:

  AND c.DateCreated >= dateadd(yy, -5, dateadd(dd, datediff(dd, 0, getdate()), 0) )

  AND c.DateCreated < getdate()

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  November 10, 2008 at 10:17 am

  #896410

  well other than the fact that you missing half the BETWEEN statement I would say yes almost there.

  c.Date > = DATEDIFF(yy,-5,GETDATE())

  Please not the this will take into account time as well.

  Thanks

  Chris

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  November 10, 2008 at 10:32 am

  #896425

  Hi Arthur

  It depends, i would use >datadiff 🙂

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 10, 2008 at 12:38 pm

  #896513

  CrazyMan (11/10/2008)

  ---

  Hi Arthur

  It depends, i would use >datadiff 🙂

  And would be wrong.

  _____________
  Code for TallyGenerator

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 10, 2008 at 1:50 pm

  #896556

  Christopher Stobbs (11/10/2008)

  ---

  well other than the fact that you missing half the BETWEEN statement I would say yes almost there.

  c.Date > = DATEDIFF(yy,-5,GETDATE())

  Please not the this will take into account time as well.

  Thanks

  Chris

  You'd want to use this, and it still has the issue with the time portion:

  c.Date > = DATEADD(yy,-5,GETDATE())