All geek questions in one place

Convert string with format "YYYYMMDDHHMMSS" to datetime

I know that there have already been many questions about converting strings to datetime , but I have not found anything to convert a string like 20120225143620 , which includes seconds.

I tried to perform a pure conversion without a substring of each segment and concatenation using / and :

Does anyone have any suggestions?

+5
source share
1 answer

You can use the STUFF() method to insert characters in a string to format it to a value that SQL Server can 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

Conclusion:

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

This approach will work if your string always has the same length and format and runs from the end of the string to the beginning to get a value in this format: YYYYMMDD HH:MM:SS

To do this, you do not need to separate the date part separately, since SQL Server will be able to understand it when formatting it.

Related reading:

STUFF (Transact-SQL)

The STUFF function inserts a row into another row. It deletes the specified character length in the first line at the start position, and then inserts the second line in the first line at the start position.

STUFF (character expression, start, length, replacementWith_expression)

+7
source

Source: https://habr.com/ru/post/1214011/

More articles:


All Articles