如何在sql server 2008中减去两个时间值。我在存储过程中使用时间变量。
请帮帮忙
发布于 2010-10-05 19:19:23
您可以使用DATEDIFF()
SELECT DATEDIFF(Day, startDate, endDate)
FROM table
SELECT DATEDIFF(Second, date, GETDATE())
FROM table
发布于 2015-09-29 16:17:28
DECLARE @END TIME = '16:00:00.0000000' ,
@START TIME = '01:00:00.0000000'
SELECT convert(TIME,dateadd(ms,DateDiff(ss, @START, @END )*1000,0),114)
发布于 2019-12-02 13:29:01
即使您使用的是偏移值或正常日期,此代码也会给出适当的答案。
BEGIN TRY
DECLARE @OffSetVal1 VARCHAR(39) = '2019-12-02 09:15:29 +14:00'
, @OffSetVal2 VARCHAR(39) = '2019-12-02 09:15:29 +12:30'
, @minutes INT = 0
, @OffSetDiff VARCHAR(19) = ''
, @HourDiff INT = 0
SET @HourDiff = DATEDIFF(HOUR,@OffSetVal1,@OffSetVal2) -- To Check digits of hours.
SET @minutes = DATEDIFF(MINUTE,@OffSetVal1,@OffSetVal2) -- To Convert minutes to hours.
SET @OffSetDiff = @minutes / 60 + (@minutes % 60) / 100.0 -- To Check '+' Or '-' And To Get Value.
SELECT CASE WHEN CAST(@OffSetDiff AS FLOAT) <= 0
THEN (CASE WHEN @HourDiff < 10
THEN FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00')
ELSE FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00')
END)
ELSE (CASE WHEN @HourDiff < 10
THEN '+'+FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00')
ELSE '+'+FORMAT(CAST(@OffSetDiff AS FLOAT),'0#.00')
END)
END
END TRY
BEGIN CATCH
PRINT N'It seems you provided an invalid DateTimeOffSet Parameter. '
END CATCH
ANS :- +01.30 (#小时和分钟的偏移差异,如果你不想要'+‘,只需从代码中删除,然后运行代码)
https://stackoverflow.com/questions/3863125
复制相似问题