如何获得1/4 2/4 3/4和4/4分钟的ms sql

问题描述：

它有点难以解释，但我会尽力的。我有一对夫妇的价值观像HH：MM 48:12 56:52 11:59如何获得1/4 2/4 3/4和4/4分钟的ms sql

我需要在个封闭的季度值。

48.12 Should be 48.25 
56.52 Should be 52.75 
11:59 Should be 12:00

是SQL Server可以

下面是我的查询。 TotalTime is varchar

SELECT (datediff(second, 0, TotalTime)/(60.0 * 60.0))

我不能使用CTE。我无法创建一个函数。它需要完成选择查询。

什么是数据类型？ –

数据类型是varchar。我更新了我的问题 – maxspan

'TotalTime'的数据类型是'VARCHAR'？ –

答

可以这样做。此函数取自其他帖子，关于时间四舍五入

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) 
RETURNS datetime 
AS 
BEGIN 
    DECLARE @RoundedTime smalldatetime 
    DECLARE @Multiplier float 

    SET @Multiplier= 24.0/@RoundTo 

    SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar,@Time,121) AS datetime) AS float) * @Multiplier,0)/@Multiplier 

    RETURN @RoundedTime 
END 

DECLARE @A VARCHAR(5) 

SELECT @A = CONVERT(CHAR(5),CAST(dbo.roundtime('13:14',0.25) AS TIME),108) 


select LEFT(@A,2) + '.' + CAST(CAST(SUBSTRING(@A,4,2) AS FLOAT)/60 * 100 AS CHAR(2))

我无法创建功能。它需要完成选择查询。 – maxspan

@maxspan这是越来越可笑...你可以使用*作为选择查询中的函数* – Shnugo

我知道你可以使用函数，但我不允许创建函数。 – maxspan

答

将cte替换为表名。像这样的东西，我想：

;WITH cte AS (
SELECT * 
FROM (VALUES 
('48:12'), 
('45:30'), 
('56:52'), 
('11:59'), 
('11:00') 
) as t(v) 
) 

SELECT CASE WHEN CAST(RIGHT(c.v,2) as int) between 46 and 59 THEN CAST((CAST(LEFT(c.v,2) as int) + 1) as nvarchar(2)) + ':' 
      WHEN CAST(RIGHT(c.v,2) as int) = 0 THEN LEFT(c.v,2) + ':' 
      ELSE LEFT(c.v,2) + '.' END 
      + 
     CASE WHEN CAST(RIGHT(c.v,2) as int) between 1 and 15 THEN '25' 
     WHEN CAST(RIGHT(c.v,2) as int) between 16 and 30 THEN '50' 
     WHEN CAST(RIGHT(c.v,2) as int) between 31 and 45 THEN '75' 
     WHEN CAST(RIGHT(c.v,2) as int) between 46 and 59 THEN '00' 
     WHEN CAST(RIGHT(c.v,2) as int) = 0 THEN '00' END as ColumnName 
FROM cte c

输出：

ColumnName 
48.25 
45.50 
57:00 
12:00 
11:00

我无法使用CTE。它需要在选择查询中完成 – maxspan

@maxspan * CTE和select查询*有什么问题？ – Shnugo

我们可以肯定，两部分都是两位数字吗？有没有领先的零？ – Shnugo

答

DECLARE @TimeString VARCHAR(MAX) = '17:34' 
DECLARE @Time Time = CAST(@TimeString AS TIME) 
SELECT DATEPART(hour,@Time) + (DATEPART(minute,@Time)/15) * 0.25 + ROUND(CAST(DATEPART(minute,@Time) % 15 AS FLOAT)/15, 0) * 0.25

嗨丹尼斯它非常接近。我只需要HH：MM。 – maxspan

你能删除秒吗？我不需要秒只是小时和分钟 – maxspan

是否有可能从我不需要秒的时间中删除。 – maxspan

答

也许这，没有CTE，无功能，只是SELECT

DECLARE @tbl TABLE(funnytime VARCHAR(5)); 
INSERT INTO @tbl VALUES('48:12'),('56:52'),('11:59'); 

SELECT CAST('00:' + REPLACE(STR(FinalTbl.TheMinute,2),' ','0') + ':' + REPLACE(STR(FinalTbl.TheSecond,2),' ','0') AS TIME) TheRoundedTime 
FROM 
(
    SELECT CASE WHEN TheSecond BETWEEN 52 AND 60 THEN TheMinute+1 ELSE TheMinute END AS TheMinute 
      ,CASE WHEN TheSecond BETWEEN 0 AND 6 THEN 0 
       WHEN TheSecond BETWEEN 7 AND 21 THEN 15 
       WHEN TheSecond BETWEEN 22 AND 36 THEN 30 
       WHEN TheSecond BETWEEN 37 AND 51 THEN 45 
       ELSE 0 END TheSecond 
    FROM 
    (
     SELECT Splitted.ToXML.value('/x[1]','int') TheMinute 
       ,Splitted.ToXML.value('/x[2]','int') TheSecond 
     FROM 
     (
      SELECT CAST('<x>' + REPLACE(funnytime ,':','</x><x>') + '</x>' AS XML) ToXML 
      FROM @tbl 
     ) AS Splitted 
    ) AS TimeVals 
) AS FinalTbl

在你的问题中，它听起来像你需要这个'.25/.30/.45'。在这种情况下，让'CASE'返回这些值并省略最后的'CAST'到'TIME'... – Shnugo

答

这里就是答案。

DECLARE @TotalTime varchar(max) 
SET @TotalTime = '48:12' 
    Select ROUND((DATEDIFF(MINUTE,0, CAST(@TotalTime as Time))/60.00)/25, 2) * 25 AS [Rounded Hours]

这会给我48:25作为答案。

我很担心，将时间投给“48:12”最终会出错，尽可能高'时间'是23:59 –

当我四舍五入时适合我 – maxspan

如何获得1/4 2/4 3/4和4/4分钟的ms sql

相关推荐