问题描述
我知道已经发布了很多关于将字符串转换为 datetime 的问题，但我还没有找到任何可以转换像 p>20120225143620 这样的字符串，其中包括秒数.
我试图执行干净的转换，而不是对每个段进行子串化并与 / 和 : 连接.
有人有什么建议吗?
您可以使用 STUFF() 方法将字符插入到您的字符串中，以将其格式化为 SQL Server 将能够理解:
DECLARE @datestring NVARCHAR(20) = '20120225143620'-- 所需格式:'20120225 14:36:20'SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')选择转换(日期时间，@datestring)作为格式化日期输出:
格式化日期========================2012-02-25 14:36:20.000如果您的字符串始终具有相同的长度和格式，则此方法将起作用，并且它从字符串的末尾到开头都可以生成以下格式的值:YYYYMMDD HH:MM:SS
为此，您无论如何都不需要分隔日期部分，因为 SQL Server 将能够理解它的格式.
相关阅读:
STUFF (Transact-SQL)
<块引用>STUFF 函数将一个字符串插入另一个字符串.它在开始位置删除第一个字符串中指定长度的字符，然后将第二个字符串插入到开始位置的第一个字符串中.
STUFF ( character_expression , start , length , replaceWith_expression )
I recognize there has been many questions posted about converting strings to datetime already but I haven't found anything for converting a string like 20120225143620 which includes seconds.
I was trying to perform a clean conversion without substring-ing each segment out and concatenating with / and :.
Does anyone have any suggestions?
You can use the STUFF() method to insert characters into your string to format it in to a value SQL Server will be able to understand:
DECLARE @datestring NVARCHAR(20) = '20120225143620'
-- desired format: '20120225 14:36:20'
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
SELECT CONVERT(DATETIME, @datestring) AS FormattedDate
Output:
FormattedDate
=======================
2012-02-25 14:36:20.000
This approach will work if your string is always the same length and format, and it works from the end of the string to the start to produce a value in this format: YYYYMMDD HH:MM:SS
For this, you don't need to separate the date portion in anyway, as SQL Server will be able to understand it as it's formatted.
Related Reading:
STUFF (Transact-SQL)
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
STUFF ( character_expression , start , length , replaceWith_expression )
这篇关于将具有 'YYYYMMDDHHMMSS' 格式的字符串转换为日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！