将“ YYYYMMDDHHMMSS”格式的字符串转换为日期时间 [英] Convert a string with 'YYYYMMDDHHMMSS' format to datetime

问题描述

我知道已经发布了很多有关将字符串转换为 datetime 的问题，但是我没有发现任何用于转换字符串的内容，例如 20120225143620 ，其中包括秒。

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 :.

有人有什么建议吗？

推荐答案

您可以使用 STUFF（）方法将字符插入字符串以将其格式化为SQL Server将能够理解的值：

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

输出：

FormattedDate
=======================
2012-02-25 14:36:20.000

如果您的字符串的长度和格式始终相同，则此方法将起作用，并且从字符串的末尾到开始产生格式如下的值： YYYYMMDD HH：MM：SS

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

为此，您不需要在无论如何，因为SQL Server将能够理解

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.

相关阅读内容：

STUFF（Transact-SQL）

STUFF函数将一个字符串插入另一个字符串。它将删除起始位置第一个字符串中指定长度的字符，然后将第二个字符串插入起始位置中的第一个字符串。

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 ）

STUFF ( character_expression , start , length , replaceWith_expression )

这篇关于将“ YYYYMMDDHHMMSS”格式的字符串转换为日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！