How to find the number of days between two dates

I would use the DATE_DIFF function to provide this value as below:

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

EDIT: IF using MySQL you omit the 'd' leaving you with

DATEDIFF(dtLastUpdated, dtCreated) AS Difference

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))

To find the number of days between two dates, you use:

DATEDIFF ( d, startdate , enddate )

Tags:

Sql

Sql Server