有没有更好的方法将 SQL 日期时间从 hh:mm:ss 转换为 hhmmss？

Question

我必须编写一个 SQL 视图，以 hhmmss 格式的字符串形式返回日期时间列的时间部分（显然 SAP BW 不理解 hh:mm:ss）。

此代码是 SAP 推荐的方法来执行此操作，但我认为必须有一种更好、更优雅的方法来完成此操作

TIME = case len(convert(varchar(2), datepart(hh, timecolumn)))
             when 1 then       /* Hour Part of TIMES */
               case convert(varchar(2), datepart(hh, timecolumn))
                 when '0' then '24'    /* Map 00 to 24 ( TIMES ) */
                 else '0' + convert(varchar(1), datepart(hh, timecolumn))
               end
            else convert(varchar(2), datepart(hh, timecolumn))
            end
         + case len(convert(varchar(2), datepart(mi, timecolumn)))
              when 1 then '0' + convert(varchar(1), datepart(mi, timecolumn))
              else convert(varchar(2), datepart(mi, timecolumn))
           end
         + case len(convert(varchar(2), datepart(ss, timecolumn)))
              when 1 then '0' + convert(varchar(1), datepart(ss, timecolumn))
              else convert(varchar(2), datepart(ss, timecolumn))
           end

这实现了所需的结果，21:10:45 显示为 211045。

我喜欢更紧凑的东西并且易于阅读，但到目前为止我还没有想出任何有效的方法。

Answer 1

注意：

问题表明该列的数据类型为DATETIME，而不是较新的(SQL Server 2008)TIME 数据类型。

答案：

  REPLACE(CONVERT(VARCHAR(8),timecolumn,8),':','')

让我们来解压一下。

，CONVERT 将日期时间的时间部分格式化为 varchar，格式为“hh:mi:ss”（24 小时制），由格式样式值 8 指定。

首先 REPLACE 函数删除冒号，以获取 'hhmiss' 格式的 varchar。

这应该足以获得您需要的格式的可用字符串。

---

后续问题

（OP问题提出）

内联表达式是否比用户定义的函数更快/更少的服务器密集度？

快速回答是肯定的。 更长的答案是：这取决于几个因素，并且您确实需要测量性能以确定这是否属实。

我创建并执行了一个基本测试用例：（

    -- sample table
    create table tmp.dummy_datetimes (c1 datetime)

    -- populate with a row for every minute between two dates
    insert into tmp.dummy_datetimes
    select * from udfDateTimes('2007-01-01','2009-01-01',1,'minute')

    (1052641 row(s) affected)

    -- verify table contents
    select min(c1) as _max
         , max(c1) as _min
         , count(1) as _cnt
      from tmp.dummy_datetimes

    _cnt    _min                    _max
    ------- ----------------------- -----------------------
    1052641 2007-01-01 00:00:00.000 2009-01-01 00:00:00.000

注意，udfDateTimes 函数返回指定间隔内两个日期时间值之间的所有日期时间值的集合。在本例中，我用整整两年内每分钟的行填充了虚拟表。 。

现在，用户定义的函数使用相同的语法执行与内联表达式相同的转换：

    CREATE FUNCTION [tmp].[udfStrippedTime] (@ad DATETIME)
    RETURNS VARCHAR(6)
    BEGIN
    -- Purpose: format time portion of datetime argument to 'hhmiss'
    -- (for performance comparison to equivalent inline expression)
    -- Modified:
    -- 28-MAY-2009 spencer7593

       RETURN replace(convert(varchar(8),@ad,8),':','')

    END

注意：我知道该函数未定义为确定性的 （我认为这需要使用模式绑定和其他一些声明来声明该函数，例如 Oracle 所需的 PRAGMA。）但是由于每个日期时间值在表中都是唯一的，因此这并不重要。函数必须对每个不同的值执行，即使它被正确地声明为DETERMINISTIC，

我不是这里的SQL Server“用户定义函数”专家，所以可能还有其他东西。我错过了这会无意中且不必要地减慢该功能。

好的。

因此，在测试中，我交替运行这些查询中的每一个，先是一个，然后是另一个，一遍又一遍地连续运行。 第一次运行的时间与后续运行的时间一致。 （通常情况并非如此，我们希望忽略第一次运行的时间。）SQL Server Management Studio 将查询运行时间报告为最接近的秒数，格式为 hh:mi:ss，这就是我在这里报告的内容。

    -- elapsed times for inline expression
    select replace(convert(varchar(8),c1,8),':','') from tmp.dummy_datetimes

    00:00:10
    00:00:11
    00:00:10

    -- elapsed times for equivalent user defined function
    select tmp.udfStrippedTime(c1) from tmp.dummy_datetimes

    00:00:15
    00:00:15
    00:00:15

对于此测试用例，我们观察到用户定义的函数比等效的内联表达式慢大约 45%。

华泰

Answer 2

您可以使用用户定义的函数，例如：

create FUNCTION [dbo].[udfStrippedTime]
(
    @dt datetime
)
RETURNS varchar(32)
AS
BEGIN
    declare @t varchar(32)
    set @t = convert( varchar(32), @dt, 108 )
    set @t = left(@t,2) + substring(@t,4,2)

    RETURN @t
END

然后将

select dbo.udfStrippedTime(GETDATE())

秒的逻辑留给读者作为练习

Answer 3

这里有一个问题。 格式化是否需要在数据库服务器上进行？ 服务器本身实际上只关心数据的存储，并且针对数据的存储进行了优化。 查看数据通常是 Db 之上的 hte 层的责任（从严格的学术意义上来说，现实世界有点混乱）

例如，如果您将结果输出到绑定到 GridControl 的 ASP.NET 页面中，您将只需在绑定到列时指定 DataFormattingString 即可。 如果您使用 C# 将其写入文本文件，那么当您提取数据时，您只需将格式字符串传递给 .ToString() 函数。

如果您需要它专门位于 DbServer 上，那么几乎每个解决方案都会很混乱，因为您需要的时间格式虽然紧凑且符合逻辑，但不是服务器能够识别的时间格式，因此您需要对其进行操作作为字符串。

Answer 4

这处理 00 -> 24 转换

SELECT CASE WHEN DATEPART(hh,timecolumn) = 0 
    THEN  '24' + SUBSTRING(REPLACE(CONVERT(varchar(8),timecolumn, 108),':',''),3,4)
    ELSE REPLACE(CONVERT(varchar(8),timecolumn, 108),':','') END

Answer 5

编辑2：更新为句柄0 --> 24 转换，以及较短的版本：

select replace(left(replace(convert(char,getdate(),8),':',''),2),'00','24') + right(replace(convert(char,getdate(),8),':',''),4)

回到稍长的版本:)

Answer 6

选择替换（转换（varchar（15），日期时间字段，108），'：'，''）
从表

Answer 7

选择替换('2009-05-27 12:49:19', ':', '')
2009-05-27 124919