0

I'm using SQL Server (T-SQL) 2016 and I would like to find the best vay to extract date from string. I dont know date starting index but I know that in given string there will be only one occurence.

DECLARE @myString AS NVARCHAR(max) = 'test string with 1/1/2023 date'
SELECT Substring(@myString, PATINDEX('[0-9]*/[0-9]*/[0-9]*', @myString), 8)

This one will work no matter where date starts but it assumes that date is 8 character, but if it would be 12/12/2023 then it will now work. Text may be changed with the time but format of date will remain the same. So in other words I need a code to extract date of given format from string. What is the best way to do that in t-sql?

1

1 Answer 1

3

If you are using SQL 2016 or later, I would use this method. It's fairly fast and requires minimal string manipulation. I use string split on the space character, then only return the results that can be coerced to a date. This has the advantage that if there are multiple dates then they will all come back (although you said there is only ever one occurrence of a datelike string).

If you don't have STRING_SPLIT, then there are methods for doing the equivalent without it, just search around.

DECLARE @myString AS NVARCHAR(max) = 'test string with 1/1/2023 date'

SELECT P.[value]
FROM STRING_SPLIT(@myString, ' ') AS P
WHERE TRY_CONVERT(DATE, P.[value]) IS NOT NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.