SQL Server 2005中的毫秒分隔符错误

Question

我真的不明白SELECT CONVERT(NVARCHAR(30), GETDATE(), 114)指令为什么输出

13:04:31:997

而不是

13:04:31.997

为什么毫秒分隔符是:而不是.？如果您需要知道，我需要能够检查某个时间是否在表中存储为XML的两个时间值之间。问题是，这个表中的分隔符是.，这对我来说很正常，所以使用两种格式之间的比较会返回错误的结果，因为一种使用.，另一种使用:。有没有什么方法可以在不使用像REPLACE这样的字符串函数或不更改表中存储的格式的情况下，使用.分隔符来获取时间？

Answer 1

这正是文档对样式114:的描述

hh:mi:ss:mmm（24小时）

我不知道有什么方法可以改变使用的分隔符。我建议将XML格式的值转换为日期时间，而不是将当前日期转换为文本值。毕竟，你在逻辑上比较日期和时间，而不是字符串。

Answer 2

我认为在进行比较时，应该尽量将格式保持为datetime。在SQLServer2008中，有一种新的数据类型time会有所帮助，但在SQLServer2005中，我们不得不使用datetime。

我猜您使用convert(..., 114)只是从datetime变量中获取时间部分。您可以使用此代码删除日期部分，但仍然有一个datetime变量。

declare @dt datetime
set @dt = '2001-01-01T10:01:02.000'
select dateadd(d, datediff(d, @dt, 0), @dt)

结果：

1900-01-01 10:01:02.000

使用.value从XML中提取数据时，需要指定要使用的数据类型。如果XML中只有时间部分，并且指定了datetime，则会得到日期为"1900-01-01"的时间。因此，您可以像这样使用datetime进行比较。

declare @T table (ID int, XMLCol xml)
insert into @T
select 1, '<t1>10:01:01.123</t1><t2>10:01:02.123</t2>' union all
select 2, '<t1>11:01:01.123</t1><t2>11:01:02.123</t2>'
declare @dt datetime
set @dt = '2001-01-01T10:01:02.000'
select T.ID
from @T as T
where dateadd(d, datediff(d, @dt, 0), @dt) between 
        T.XMLCol.value('t1[1]', 'datetime') and 
        T.XMLCol.value('t2[1]', 'datetime')  

结果：

ID
1