0

hoping someone can help. I have an textbox on a report that I need to calculate a new date value for from another date on the report.

I need to take an existing shipping date (say 5/5/2014) and calculate a new date which is one year later but on the Monday of that week (so would be 5/4/2015) -- we only ship product on Mondays.

I know I need to use DateAdd and probably DatePart but have been unable to find the proper formula.

can anyone help?

Etheryte
  • 24,589
  • 11
  • 71
  • 116
Kim Jones
  • 123
  • 1
  • 6
  • 15
  • Try `DATEPART(WK, myDate)` with `YEAR(myDate)+1` and the following question/answers should help you out: http://stackoverflow.com/questions/607817/get-dates-from-a-week-number-in-t-sql – Menno Jun 01 '14 at 20:33
  • doesn't "wk" get a week number? I need to get the date that's one year later but on the Monday of that week? – Kim Jones Jun 01 '14 at 20:41

1 Answers1

0

figured it out -- thanks for all the help. I took the idea that Gordon had, putting it in my select statement rather than trying to manipulate it in the expression area of the textbox on the report:`SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(YY,1,o.REQ_DATE)),0) as NewDate'

works great! :)

Kim Jones
  • 123
  • 1
  • 6
  • 15