SQL 将 varchar 转换为时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14293617/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert varchar to time
提问by Zanam
I have tried the links available here for similar questions before but it does not work. So I am posting my case here.
我之前尝试过这里提供的链接来解决类似的问题，但它不起作用。所以我在这里发布我的案例。
I have time in format:
我有时间的格式：
09:47:11:895799
This is stored as varchar(50)in my Microsoft SQL Server database.
它存储varchar(50)在我的 Microsoft SQL Server 数据库中。
I am trying to convert it to time format as on this link.
我正在尝试将其转换为此链接上的时间格式。
hh:mm:ss[.nnnnnnn]
Can you please help?
你能帮忙吗？
回答by Martin Smith
The string is in the wrong format. It should be 09:47:11.895799. You need to replace the final :with a .
字符串格式错误。应该是09:47:11.895799。你需要:用一个.
SELECT CAST(STUFF('09:47:11:895799', 9,1,'.') AS TIME)
回答by Kaf
You can also make your string as hh:mi:ss:mmm(24 hour format) and then convertas time. That means trimming out last 3 digits from your milli seconds without replacing last ':'with '.'
您还可以将字符串设为hh:mi:ss:mmm（24 小时格式），然后convert设为时间。这意味着微调从你的毫秒后3位，而无需更换最后':'用'.'
Here is MSDN linkfor more details of formatting. Here is working example of SQL Fiddle
这是有关格式化的更多详细信息的MSDN 链接。这是SQL Fiddle 的工作示例
DECLARE @s varchar(50) = '09:47:11:895799'
SELECT Convert(time, Left(@s,Len(@s)-3), 114)
