Convert "yyyymmddhhmiss" Text to DateTime

userPzE2 0
Viewed 21

We have a vendor database in which for some unknown reason all of the datetime values are actually nvarchar(14) in the format of "yyyymmddhhmiss". I have been hunting for a T-SQL function to do something like this....

FORMAT(TextDatetime, "yyyymmddhhmiss")

Unfortunately, it looks like FORMAT() only takes numeric or date and time datatypes.

I could create user-defined scalar function like this...

CREATE FUNCTION udf_Convert_TextDatetime
(
    @TextDateTime nvarchar(14)
)
RETURNS datetime
AS
BEGIN

    RETURN DATETIMEFROMPARTS (
                                 SUBSTRING (@TextDateTime, 1, 4)
                               , SUBSTRING (@TextDateTime, 5, 2)
                               , SUBSTRING (@TextDateTime, 7, 2)
                               , SUBSTRING (@TextDateTime, 9, 2)
                               , SUBSTRING (@TextDateTime, 11, 2)
                               , SUBSTRING (@TextDateTime, 13, 2)
                               , '0'
                             )
END;

The volume of data in this database is small so I am not that concerned about performance.

Is this user-defined function the best solution here?

tsql
1 Answers

You can create a function that utilizes STUFF to create a properly formatted DATETIME value.

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.

CREATE FUNCTION udf_Convert_TextDatetime
(
    @TextDateTime nvarchar(14)
)
RETURNS datetime
AS
BEGIN
    RETURN STUFF(STUFF(STUFF(STUFF(STUFF(@TextDateTime,5,0,'-'),8,0,'-'),13,0,':'),16,0,':'),11,0,' ')
END;

Input:

test_dt
20220914010101
20220915185700
20220916234559 
SELECT dbo.udf_Convert_TextDatetime(test_dt) AS datetime FROM test

Output:

datetime
2022-09-14 01:01:01.000
2022-09-15 18:57:00.000
2022-09-16 23:45:59.000

db<>fiddle here.

GRIV0
Related