Check if fields in a column are dates and convert them

Question

I am creating a process that will eventually get data from one place and place it into another. For this to happen, I need to transform my data.

I have a column that contains string's and dates

timestamp
20POST /xxxei/adsa/ContentVis HTTP/1.1  Host: fa.aweb.com
2016-12-18T00:15:10.289Z
2016-11-06T04:09:54.503Z

I was doing this:

select cast(left("timestamp",10) as timestamp)

but now my process is failing because I didn't account for this, as a value:

20POST /xxxei/adsa/ContentVis HTTP/1.1  Host: fa.aweb.com

My system doesn't take the function ISDATE(), do you guys have any ideas this cinco de Mayo.

Answer 1

You can use a funciton like DAY() on that string. if it returns null, it's not a valid date

More specific to your case:

SELECT DAY('20POST /xxxei/adsa/ContentVis HTTP/1.1  Host: fa.aweb.com') IS NOT NULL as is_date;

SELECT DAY('2016-12-18T00:15:10.289Z') IS NOT NULL as is_date;