GETDATE - number of dates - a column / division in SQL

Question

I need to do a calculation like this:

(Get today's date - 219 - HISTSTARTDATE)  / 7 AS 'Weeks Of History'

When I do it like this

(DATEDIFF(DAY, 0, GETDATE()) - DATEADD(DAY, -219, GETDATE()) - HISTSTART) / 7 AS 'Weeks Of History'

I get an error

The data types datetime and date are incompatible in the subtract operator

I am pretty confused because I believe it was working for me before. Does someone may have an idea what can be wrong and how to fix it?

Please let me know if you know.

Answer 1

The reason it's not working in SQL we can't subtract to date like below

Wrong: '2020-02-09' - '2020-01-02'
Right : DateDiff(day/hour/year, '2020-02-09' ,'2020-01-02')

So your answer has to be like this :

DateDiff(Day, (Get today's date) , DateDiff(day, dateadd(), HISTSTARTDATE) )/ 7 AS 'Weeks Of History'

Code:

Select DATEDIFF(DAY,DATEDIFF(DAY,0,GETDATE()) ,cast(DATEADD(DAY, -219, GETDATE()) - HISTSTART as date)) / 7  AS 'Weeks Of History'