Convert A String With 'yyyymmddhhmmss' Format To Datetime

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 include

Solution 1:

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,' ')

SELECTCONVERT(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 )