时间戳

首页 > 解决方案 > 将浮点数转换为分和秒

sql - 将浮点数转换为分和秒

问题描述

变量 Duration 是一个浮点数。如何将其转换为可用且可求和的时间格式？

请参阅下面的示例数据，其中 Time_Duration 是想要的结果：

SELECT  '00:00:21.0000000' AS Duration ,  '00:21' AS Time_Duration  UNION
SELECT  '00:01:15.0000000' AS Duration,   '01:15' AS Time_Duration  UNION
SELECT  '00:01:43.0000000' AS Duration,   '01:43' AS Time_Duration  UNION
SELECT  '00:05:50.0000000' AS Duration ,  '05:50' AS Time_Duration

我尝试先将浮点数转换为小数，如下所示：

  select 
     ,FORMAT(FLOOR([Duration])*100 + ([Duration]-FLOOR([Duration]))*60,'00:00') as Time_Duration
     FROM [dbo].[mobile]

但我收到以下错误：

操作数类型冲突：时间与浮点数不兼容

如何使“持续时间”可用？

标签： sqlsql-servertsql

解决方案

您可以使用下面给出的简单格式功能。

注意：我们需要:用\. 请参阅 MSDN 参考

declare @table table(duration varchar(20), timeduration time)
insert into @table
SELECT  '00:00:21.0000000' AS Duration ,  '00:21' AS Time_Duration  UNION
SELECT  '00:01:15.0000000' AS Duration,   '01:15' AS Time_Duration  UNION
SELECT  '00:01:43.0000000' AS Duration,   '01:43' AS Time_Duration  UNION
SELECT  '00:05:50.0000000' AS Duration ,  '05:50' AS Time_Duration

select format(cast(duration as time),N'mm\:ss') as time_duration from @table
time_duration
00:21
01:15
01:43
05:50

推荐阅读