将具有 'YYYYMMDDHHMMSS' 格式的字符串转换为日期时间

2022-01-16 00:00:00 datetime datetime-format sql-server sql-server-2008-r2

我知道已经发布了很多关于将字符串转换为 datetime 的问题，但我还没有找到任何可以转换像 20120225143620 这样的字符串，其中包括秒数.p>

我试图执行干净的转换，而不是对每个段进行子串化并与 / 和 : 连接.

有人有什么建议吗?

解决方案

您可以使用 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.