Extract Free Text Field Dates

Question

Extract Free Text Field Dates

Navigation

#1 by (2 votes)

2

I'm running a query in a view, but one of its fields is text, in this field the user can enter a date (xx / xx / xxxx) as well as any other type information. I would like to know how I can return only the records I can convert to date or how can I handle the error below, what happens when I try to convert the field to the type I need?

Error: Conversion failed when converting date and / or time from character string.

Query 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

sql-server sql-server-2008

asked by anonymous 20.09.2017 / 19:58

1 answer

How to call a html function Error Code: 1054. Unkowm column

score 2 · Accepted Answer

A good solution would be to use regular expressions. If I remember correctly they are natively supported on newer 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 that function you must use some external component. Windows comes with a few. From this page I have removed an example from a function which 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 whatever other term suits you best.)