SQL Subtract exactly a year

Based on your comment regarding hard coded year values, the use of

 DATEDIFF(year,BOOKED,GETDATE())

to get the number of years since the date you're after should hopefully lead you in the direction you are after.

You will probably end up with something like:

SELECT DATEADD(year, -DATEDIFF(year,BOOKED,GETDATE()), GETDATE())

Ok, it looks more like all you really want to do (I may be wrong, sorry if so) is to group the bookings by year.

Will the result of the following help achieve that?

SELECT SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0), Sum(APRICE) as Total, Sum(PARTY) as PAX
FROM  DataWarehouse.dbo.B01Bookings AS B101Bookings
GROUP BY SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0)

As I said, this is a guess as to your goal, not necessarily an answer to your question.


Use this:

dateadd(year, -1, getdate())