How to create a function to split date and time from a string in SQL?

Question

How can I remove value before '_' and show date and time in one row in TSQL Function?

Below is sample:

Declare @inputstring as varchar(50) = 'Studio9_20230126_203052' ;

select value from STRING_SPLIT( @inputstring ,'_')

Output Required: 2023-01-26 20:30:52.000

Answer 1

Creating function

CREATE FUNCTION [dbo].[convert_to_date] (@inputstring NVARCHAR(MAX))
RETURNS DATETIME AS
BEGIN 

        DECLARE @finalString varchar(50), @out varchar(100)

        SET @finalString = REPLACE ( (SUBSTRING (@inputstring, CHARINDEX('_', @inputstring)+1 , LEN(@inputstring))), '_', ' ')
                    
        --SELECT @finalString

        SET @out  =  LEFT (@finalString, 4) + '-'               
                    + SUBSTRING(@finalString, 5, 2) + '-' 
                    + SUBSTRING(@finalString, 7, 2) + ' '
                    + SUBSTRING(@finalString, 10, 2) + ':'
                    + SUBSTRING(@finalString, 12, 2) + ':'
                    + SUBSTRING(@finalString, 14, 2) + '.000'
            
RETURN  @out
END

Select Query

SELECT dbo.[convert_to_date] ('Studio54541659_20230126_203052')

Output

2023-01-26 20:30:52.000