此代码以yyyy MM dd格式计算两个日期之间的差值。

declare @StartDate datetime 
declare @EndDate datetime

declare @years int
declare @months int 
declare @days int

--NOTE: date of birth must be smaller than As on date, 
--else it could produce wrong results
set @StartDate = '2013-12-30' --birthdate
set @EndDate  = Getdate()            --current datetime

--calculate years
select @years = datediff(year,@StartDate,@EndDate)

--calculate months if it's value is negative then it 
--indicates after __ months; __ years will be complete
--To resolve this, we have taken a flag @MonthOverflow...
declare @monthOverflow int
select @monthOverflow = case when datediff(month,@StartDate,@EndDate) - 
  ( datediff(year,@StartDate,@EndDate) * 12) <0 then -1 else 1 end
--decrease year by 1 if months are Overflowed
select @Years = case when @monthOverflow < 0 then @years-1 else @years end
select @months =  datediff(month,@StartDate,@EndDate) - (@years * 12) 

--as we do for month overflow criteria for days and hours 
--& minutes logic will followed same way
declare @LastdayOfMonth int
select @LastdayOfMonth =  datepart(d,DATEADD
    (s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)))

select @days = case when @monthOverflow<0 and 
    DAY(@StartDate)> DAY(@EndDate) 
then @LastdayOfMonth + 
  (datepart(d,@EndDate) - datepart(d,@StartDate) ) - 1  
      else datepart(d,@EndDate) - datepart(d,@StartDate) end 


select
 @Months=case when @days < 0 or DAY(@StartDate)> DAY(@EndDate) then @Months-1 else @Months end

Declare @lastdayAsOnDate int;
set @lastdayAsOnDate = datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate),0)));
Declare @lastdayBirthdate int;
set @lastdayBirthdate =  datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)));

if (@Days < 0) 
(
    select @Days = case when( @lastdayBirthdate > @lastdayAsOnDate) then
        @lastdayBirthdate + @Days
    else
        @lastdayAsOnDate + @Days
    end
)
print  convert(varchar,@years)   + ' year(s),   '  +
       convert(varchar,@months)  + ' month(s),   ' +
       convert(varchar,@days)    + ' day(s)   '   

你可以简单地这样做:

SELECT (end_time - start_time) FROM t; -- return in Millisecond
SELECT (end_time - start_time)/1000 FROM t; -- return in Second

此代码以yyyy MM dd格式计算两个日期之间的差值。

declare @StartDate datetime 
declare @EndDate datetime

declare @years int
declare @months int 
declare @days int

--NOTE: date of birth must be smaller than As on date, 
--else it could produce wrong results
set @StartDate = '2013-12-30' --birthdate
set @EndDate  = Getdate()            --current datetime

--calculate years
select @years = datediff(year,@StartDate,@EndDate)

--calculate months if it's value is negative then it 
--indicates after __ months; __ years will be complete
--To resolve this, we have taken a flag @MonthOverflow...
declare @monthOverflow int
select @monthOverflow = case when datediff(month,@StartDate,@EndDate) - 
  ( datediff(year,@StartDate,@EndDate) * 12) <0 then -1 else 1 end
--decrease year by 1 if months are Overflowed
select @Years = case when @monthOverflow < 0 then @years-1 else @years end
select @months =  datediff(month,@StartDate,@EndDate) - (@years * 12) 

--as we do for month overflow criteria for days and hours 
--& minutes logic will followed same way
declare @LastdayOfMonth int
select @LastdayOfMonth =  datepart(d,DATEADD
    (s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)))

select @days = case when @monthOverflow<0 and 
    DAY(@StartDate)> DAY(@EndDate) 
then @LastdayOfMonth + 
  (datepart(d,@EndDate) - datepart(d,@StartDate) ) - 1  
      else datepart(d,@EndDate) - datepart(d,@StartDate) end 


select
 @Months=case when @days < 0 or DAY(@StartDate)> DAY(@EndDate) then @Months-1 else @Months end

Declare @lastdayAsOnDate int;
set @lastdayAsOnDate = datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate),0)));
Declare @lastdayBirthdate int;
set @lastdayBirthdate =  datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)));

if (@Days < 0) 
(
    select @Days = case when( @lastdayBirthdate > @lastdayAsOnDate) then
        @lastdayBirthdate + @Days
    else
        @lastdayAsOnDate + @Days
    end
)
print  convert(varchar,@years)   + ' year(s),   '  +
       convert(varchar,@months)  + ' month(s),   ' +
       convert(varchar,@days)    + ' day(s)   '   

为什么不干脆

从表中选择Sum(Date1 - Date2)

Date1和date2是datetime

如果您正在使用DATE列(或者可以将它们转换为日期列)，请尝试DATEDIFF()，然后乘以24小时、60分钟、60秒(因为DATEDIFF返回的天数不同)。从MySQL:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

例如:

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60

如果你想添加带有DATEDIFF的where子句，那么也可以添加where子句或条件。 请看下面的例子。

select DATEDIFF(now(), '2022-08-12 17:55:51.000000') from properties p WHERE p.property_name = 'KEY';

结果:6