Extract free text field dates

Question

I am running a query in a view, but one of its fields is text, in this field the user can inform a date (xx/xx/xxxx) as well as any other information of type text. I wonder how I can return only the records I can convert to date or how I can treat the error below, which occurs when I try to convert the field into the type I need?

Error: Conversion failed when Converting date and/or time from Character string.

consultation I’m doing:

SELECT 
    V.CAMPO1, 
    V.CAMPO1, 
    V.CAMPO1,  
    V.CAMPO_CHAR_DATA, 

CASE WHEN RTRIM(V.CAMPO_CHAR_DATA) <> '' THEN       
    CONVERT(DATETIME, RTRIM(V.CAMPO_CHAR_DATA, ),103)
    ELSE '2017-01-01 00:00:00' 
END AS CAMPO_CHAR_DATA 

FROM VIEW_TESTE V   

Answer 1

A good solution would be to use regular expressions. If I remember correctly they are natively supported in the latest versions of SQL Server, but you specified the 2008 version.

In this version, to use regular expressions, you need to create a function of your own, and in this function you must use some external component. Windows comes with some. From this page I removed an example of a function that can help:

CREATE FUNCTION    
dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS int
AS
BEGIN
    declare @obj int
    declare @res int
    declare @match bit
    set @match=0
    exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
    IF (@res <> 0) BEGIN
        RETURN NULL
    END
    exec @res=sp_OASetProperty @obj, 'Pattern', @pattern
    IF (@res <> 0) BEGIN
        RETURN NULL
    END
    exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1
    IF (@res <> 0) BEGIN
        RETURN NULL
    END
    exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring
    IF (@res <> 0) BEGIN
        RETURN NULL
    END
    exec @res=sp_OADestroy @obj
    return @match
END
GO

To use:

SELECT V.CAMPO1, V.CAMPO2 -- etc, etc
FROM VIEW_TESTE V
WHERE dbo.fn_regex('[0-9][0-9]/[0-9][0-9]/[1-9][0-9][0-9][0-9]', V.CAMPO_CHAR_DATA) <> 0

(Or any other expression that suits you better).