如何获取SQL Server中日期时间差的值
问题描述:
有谁知道如何获取SQL Server中日期时间差的值?如何获取SQL Server中日期时间差的值
例如:
如果我有一个表调用客户提供以下列
Name Join_Date Exp_Date
Alvin 2010-01-01 12:30:00 2010-01-03 11:30:00
Amy 2010-01-01 15:30:00 2010-01-02 10:30:00
我怎样才能在SQL Server以下结果?
Name Join_Date Exp_Date Diff
Alvin 2010-01-01 12:30:00 2010-01-03 11:30:00 2days 1hour
Amy 2010-01-01 15:30:00 2010-01-02 10:30:00 1day 7hours
答
Select Name, Join_Date, Exp_Date
, DateDiff(d, Join_Date, Exp_Date) As [Days]
, DateDiff(hh, Join_Date, Exp_Date) As [Hours]
或者
Select Name, Join_Date, Exp_Date
, Cast(DateDiff(d, Join_Date, Exp_Date) As varchar(10)) + ' days '
+ Cast(DateDiff(hh, Join_Date, Exp_Date) As varchar(10)) + ' hours'
或者超级花哨的版本
Select Name, Join_Date, Exp_Date
, Cast(DateDiff(d, Join_Date, Exp_Date) As varchar(10))
+ Case
When DateDiff(d, Join_Date, Exp_Date) = 1 Then ' day '
Else ' days '
End
+ Cast(DateDiff(hh, Join_Date, Exp_Date) As varchar(10))
+ Case
When DateDiff(hh, Join_Date, Exp_Date) = 1 Then ' hour'
Else ' hours '
End
答
我的解决方案假定您想要的日期/时间部分的名称英文和用法UDF。
它计算日期时间之间的实际差异,即如果Exp_Date
是今天Join_Date
昨天却是他们之间不超过24小时,则天数将是0
。不显示零部件。
CREATE FUNCTION dbo.fnGetDTPart (@UnitName varchar(10), @Number int)
RETURNS varchar(50)
AS BEGIN
DECLARE @Result varchar(50);
IF @Number = 0
SET @Result = ''
ELSE
SET @Result = CAST(@Number AS varchar) + @UnitName +
CASE @Number WHEN 1 THEN ' ' ELSE 's ' END;
RETURN @Result;
END
GO
WITH Customer (Name, Join_Date, Exp_Date) AS (
SELECT
'Alvin',
CAST('2010-01-01 12:30:00' AS datetime),
CAST('2010-01-03 11:30:00' AS datetime)
UNION
SELECT
'Amy',
CAST('2010-01-01 15:30:00' AS datetime),
CAST('2010-01-02 10:30:00' AS datetime)
)
SELECT
Name,
Join_Date,
Exp_Date,
Diff =
dbo.fnGetDTPart('year', DATEPART(year, DiffDT)-1900) +
dbo.fnGetDTPart('month', DATEPART(month, DiffDT)-1) +
dbo.fnGetDTPart('day', DATEPART(day, DiffDT)-1) +
dbo.fnGetDTPart('hour', DATEPART(hour, DiffDT)) +
dbo.fnGetDTPart('minute', DATEPART(minute, DiffDT)) +
dbo.fnGetDTPart('second', DATEPART(second, DiffDT))
FROM (
SELECT
Name,
Join_Date,
Exp_Date,
DiffDT = Exp_Date - Join_Date
FROM Customer
) s
答
假设你想实际正确的时间差(如安德烈·M的注释中描述),并且只是在寻找一个用户友好的方式显示出来,你可以这样做:
select Join_Date
,Exp_Date
,cast((datediff(hour,Join_Date,Exp_Date)/24) as nvarchar) + 'days '
+ cast((datediff(hour,Join_Date,Exp_Date) % 24) as nvarchar) + 'hours' as Diff
的差值你已经显示有点混乱。你的意思是计算日期部分差异与时间部分差异分开还是只是任意数字?我的意思是`2010-01-01 12:30:00`和'2010-01-03 11:30:00'之间的实际区别是1天23小时。但你似乎显示日期差异和时间差异已被相互独立计算。 – 2011-02-02 05:57:08