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

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

CodePudding user response：

If we can safely assume that the value is always in the format {Some String}_{yyyyMMdd}_{hhmmss} then you can use STUFF a few times, firstly to remove the leading string up to the first underscore (_) character (using CHARINDEX to find that character), and then to inject 2 colon (:) characters. Finally you can REPLACE the remaining underscore with a space ( ), and then use TRY_CONVERT to attempt to convert the value to a datetime2(0).

DECLARE @inputstring varchar(50) = 'Studio9_20230126_203052';

SELECT TRY_CONVERT(datetime2(0),REPLACE(STUFF(STUFF(STUFF(@inputstring,1,CHARINDEX('_',@inputstring),''),14,0,':'),12,0,':'),'_',' '));

Note that this doesn't give the value you state you want in your question (2023-01-26 20:05:52.000) , but I assume this is a typographical error, and that the 05 for minutes should be 30.

CodePudding user response：

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