Wrong Millisecond Separator in SQL Server 2005

I really can't figure out why the instruction SELECT CONVERT(NVARCHAR(30), GETDATE(), 114) outputs, fo开发者_开发问答r instance,

13:04:31:997

instead of

13:04:31.997

Why is the milliseconds separator a : insted of a .? In case you need to know, I need to be able to check if a certain time is between two time values stored as XML in a table. The problem is that the separator in this table is, as it is normal for me, a ., so that using comparisons between the two formats returns wrong results because one uses . and the other uses :. Is there any way I can get the time with the . separator without using string functions like REPLACE or without changing the format stored in the table?

That's exactly what the documentation says for style 114:

hh:mi:ss:mmm(24h)

I don't know of any way of changing the separator used. I would suggest converting the XML-formatted value to a datetime instead of converting the current date into a textual value. After all, you're logically comparing dates and times, not strings.

I think you should try to keep the format as datetime when you do the comparison. In SQL Server 2008 there is a new data type time that would have been helpful but in SQL Server 2005 we have to use datetime instead.

I guess that you are using convert(..., 114) to only get the time part from a datetime variable. You can use this code to remove the date part and still have a datetime variable.

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

Result:

1900-01-01 10:01:02.000

When you extract data from XML using .value you specify the datatype to be used. If you only have the time part in the XML and you specify datetime you will get the time of the date '1900-01-01'. So you can do the comparison using datetime like this.

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')  

Result:

ID
1