[sql] How to find the number of days between two dates

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?

As @Forte L. mentioned you can do the following as well;

SELECT dtCreated
    , bActive
    , dtLastPaymentAttempt
    , dtLastUpdated
    , dtLastVisit

    , DATEDIFF(day, dtCreated, dtLastUpdated) Difference

FROM Customers
WHERE (bActive = 'true') 
    AND (dtLastUpdated > CONVERT(DATETIME, '2012-01-0100:00:00', 102))

Get No of Days between two days

DECLARE @date1 DATE='2015-01-01',
 @date2 DATE='2019-01-01',
 @Total int=null

SET @Total=(SELECT DATEDIFF(DAY, @date1, @date2))
PRINT @Total

If you are using MySQL there is the DATEDIFF function which calculate the days between two dates:

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

DECLARE @Firstdate DATE='2016-04-01',
 @LastDate DATE=GETDATE(),/*get today date*/
 @resultDay int=null

SET @resultDay=(SELECT DATEDIFF(d, @Firstdate, @LastDate))
PRINT @resultDay

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

DATEDIFF ( d, startdate , enddate )

The DATEDIFF function is use to calculate the number of days between the required date

Example if you are diff current date from given date in string format

SELECT * , DATEDIFF(CURDATE(),STR_TO_DATE('01/11/2017', '%m/%d/%Y')) AS days FROM consignments WHERE code = '1610000154'

Here, STR_TO_DATE () : Take a string and returns a date specified by a format mask;

For your example :

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

Tested on mysql server 5.7.17

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

DATEDIFF(d, 'Start Date', 'End Date')

do it