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 

Solution 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

Solution 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

Solution 3

The normal function to use is datediff():

select datediff(day, cast('2016-02-03' as date), cast('2016-03-30' as date))

You can subtract datetime values, but not dates. Alas.

Comments

[Dipen Patel]

Please mark as answer if this one help to get your results! Thanks 😀