如何计算两次约会之间的天数

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".

我想在输出中添加另一列……我们称它为“差异”，以找出“dtcreated”和“dtlastupdated”之间的天数，例如，如果记录1的dtcreated是1/11，而dtlastupdated是1/12，那么“差异”列将是“365”。

Can this be accomplished in a query?

这能在查询中完成吗?

5 个解决方案

#1

You would use DATEDIFF:

你会使用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))

#2

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

要找到两个日期之间的天数，您可以使用:

DATEDIFF ( d, startdate , enddate )

#3

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

我将使用DATE_DIFF函数提供如下值:

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

编辑:如果使用MySQL，你就省略了“d”

DATEDIFF(dtLastUpdated, dtCreated) AS Difference

#4

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

如果使用MySQL，则有DATEDIFF函数计算两个日期之间的天数:

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

#5

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

do it

这样做

#1