Subtract two dates in Microsoft SQL Server

Question

I want to subtract 2 dates in MS SQL Server.

Example:

Current date      Last used date
'2016-03-30'      '2015-02-03'

Current date refers to today's date, "Last used date" is a measure.

How to write a query in SQL Server?

I have this but doesn't work (it says "Operand data type is invalid for subtract operator")

select 
    CONVERT(DATE, GETDATE()) - CONVERT(DATE, LastUsedDate) 
from 
    databasename 

Answer 1

SELECT     DATEDIFF(day,'2014-06-05','2014-08-05')     AS DiffDate

Output DiffDate 61

More practice please refer below W3 school:

https://www.w3schools.com/sql/func_sqlserver_datediff.asp

Answer 2

Here you don't have to cast GETDATE() to date, as it is already datetime datatype. So your query will be as follows

SELECT DATEDIFF(day,CAST(LastUsedDate as date),GETDATE()) AS DifferneceDays
FROM TableName