在 SQL Server 2008 中将 Varchar HH:MM 转换为整数分钟的查询

Query to Convert Varchar HH:MM to Integer Minutes in SQL Server 2008

提问by Dharmendra Kumar Singh

I am having a table in SQL Server where there is a column name TimeSpent Datatype Varchar(25). It basically stores Time as HH:MMFormat. As per the requirement now i want that it gives me actual timespent in Minutes i.e. 01:00 gives me 60, 01:30 -> 90. Please help me to write the query in SQL Server 2008 , so that will convert Varchar (HH:MM) into Minutes(integer).

我在 SQL Server 中有一个表，其中有一个列名 TimeSpent Datatype Varchar(25)。它基本上将时间存储为HH:MM格式。根据现在的要求，我希望它以分钟为单位给出实际花费的时间，即 01:00 给我 60, 01:30 -> 90。请帮助我在 SQL Server 2008 中编写查询，以便转换 Varchar (HH: MM) 转换为分钟（整数）。

回答by peterm

Try

尝试

SELECT LTRIM(DATEDIFF(MINUTE, 0, TimeSpent)) 
  FROM yourtable

Given

给定的

| TIMESPENT |
-------------
|     00:12 |
|     01:05 |
|     10:00 |

Output:

输出：

| MINUTES |
-----------
|      12 |
|      65 |
|     600 |

Here is SQLFiddle

这是SQLFiddle

回答by ljh

This will solve your issue. Please refer to SQLFiddle

这将解决您的问题。请参考SQLFiddle

select cast(left(TimeSpent, charindex(':',TimeSpent,0)-1) as int)*60+
       cast(right(TimeSpent, len(TimeSpent)-charindex(':',TimeSpent, 0)) as int)
from test

回答by Michal Bolehradsky

SELECT top 1 (substring(CONVERT(VARCHAR(5), TimeSpent, 108), 1, 2) * 60 + substring(CONVERT(VARCHAR(5), TimeSpent, 108), 4, 2)) as minutes from test

SELECT top 1 (substring(CONVERT(VARCHAR(5), TimeSpent, 108), 1, 2) * 60 + substring(CONVERT(VARCHAR(5), TimeSpent, 108), 4, 2)) 作为距离测试的分钟数