SQL : Checking format of column whose value is 'Date' but type is 'String'

Question

I am running a ETL job which is failing due to data quality issues. In my source, my date column is in String format and I am converting it to a TIMESTAMP using to_timestamp() method while storing it into target.

This job is failing when one date value(in String format) comes as 2016-06- instead of 2016-11-10 06:07:48.633. Due to the incorrect value, I can't convert it to a timestamp and my whole job fails.

How do I apply a format check in a generic way to verify that date(in String data type) is in correct format before converting it to timestamp?

Possible duplicate of stackoverflow.com/q/44018443/1509264 or stackoverflow.com/q/37659643/1509264 — MT0
– MT0, Commented Sep 19, 2017 at 14:48
The real question: why are you storing timestamps in a VARCHAR column? — user330315
– user330315, Commented Sep 19, 2017 at 15:20
I agree to it completely but it is not in my control and I can't change it — Sahil Doshi
– Sahil Doshi, Commented Sep 20, 2017 at 6:01

prashant sugara · Accepted Answer · 2017-09-22 05:00:53Z

1

You could use Isdate() function to check if a string is date or not.

Use this function at filter level and filter the invalid dates and load the rest.

answered Sep 22, 2017 at 5:00

prashant sugara

3061 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL : Checking format of column whose value is 'Date' but type is 'String'

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related