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

score 15 · Accepted Answer · edited Jun 21 '19 at 08:50

15

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

Output DiffDate 61

More practice please refer below W3 school:

edited Jun 21 '19 at 08:50

Nikolai Novik

answered Dec 22 '16 at 00:21

Dipen Patel

1

Please mark as answer if this one help to get your results! Thanks – Dipen Patel Dec 22 '16 at 00:24

score 3 · Answer 2 · answered Mar 30 '16 at 05:39

3

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

answered Mar 30 '16 at 05:39

Vinay Tedla

score 2 · Answer 3 · answered Mar 29 '16 at 22:22

2

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.

answered Mar 29 '16 at 22:22

Gordon Linoff

3 Answers3