How to find the number of days between two dates

sql, sql-server

Solution

You would use `DATEDIFF`:

declare @start datetime
declare @end datetime

set @start = '2011-01-01'
set @end = '2012-01-01'

select DATEDIFF(d, @start, @end)

results = 365

so for your query:

SELECT dtCreated
    , bActive
    , dtLastPaymentAttempt
    , dtLastUpdated
    , dtLastVisit
    , DATEDIFF(d, dtCreated, dtLastUpdated) as Difference
FROM Customers
WHERE (bActive = 'true') 
    AND (dtLastUpdated > CONVERT(DATETIME, '2012-01-0100:00:00', 102))

Problem

I have a basic query: ``` SELECT dtCreated , bActive , dtLastPaymentAttempt , dtLastUpdated , dtLastVisit FROM Customers WHERE (bActive = 'true') AND (dtLastUpdated > CONVERT(DATETIME, '2012-01-0100:00:00', 102)) ``` I want to add another column to the output... lets call it "Difference" to find out the number of days between 'dtcreated' and 'dtlastupdated' So for example if record 1 has a dtcreated of 1/1/11 and dtlastupdated is 1/1/12 the "Difference" column would be "365". Can this be accomplished in a query?

Original source