View with rolling date

• Jessie DeMeyer-235369

  SSC Rookie

  Points: 43

  More actions

  April 27, 2006 at 1:50 pm

  #167866

  Hello,

  I would like to code a rolling date into a prexisting view.  Esentially, I want to pull a year's worth of sales records.  An example would be:

  If today's date is 4/27/06

  I would like to see all sales records with a DOCDATE between 4/27/05 and 4/27/06.

  Can someone give me an example of how I can code this?

  Thank you!

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  April 27, 2006 at 2:00 pm

  #634689

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp

  select * from table

  where DOCDATE <= DATEADD ( yy , -1, getdate() )

  and DOCDATE <= getdate()

• Jessie DeMeyer-235369

  SSC Rookie

  Points: 43

  More actions

  April 27, 2006 at 2:01 pm

  #634691

  Thank you!

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  April 27, 2006 at 2:10 pm

  #634693

  One word of caution: this will get down to the millisecond.  Look at Datepart to go about getting just the date...

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2mic.asp

  or Day(), Month() and Year()

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp