Convert a string with 'YYYYMMDDHHMMSS' format to datetime

Question

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?

Deuno

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

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 )

Hiltan

If datestring is GMT + 2 and I need FormattedDate like GMT + 0 ? – Noyes

YYYYMMDD HH:MM desired format: '20120225 14:36' SET @datestring = STUFF(STUFF(@datestring,9,0,' '),12,0,':') – Noyes

Answer 2

DECLARE @datestring NVARCHAR(20) = '20220822190000330';
DECLARE @dateint BIGINT = CONVERT(BIGINT,@datestring);
SELECT CONVERT(DATETIME,FORMAT(@dateint, '####-##-## ##:##:##:###')) AS FormatDate;

Output

FormatDate
-----------------------
2022-08-22 19:00:00.330

Arc

This is an interesting alternative. The answer could use some explanation though. In particular, this answer is designed to work with a 17-digit date/time string with 3 digits of fraction, instead of the 14 digits from the original post. The advantage here is that the expression could easily be adapted to several source value lengths. I suggest that you update your answer to include both cases. – Lovellalovelock

Answer 3

SELECT format(getdate(),'yyyyMMddHHmmssffff')

Please run the below sql command for unique key generation with timestamp

Lucia

This does not convert a string to a datetime – Piddle