0

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?

MT0
  • 143,790
  • 11
  • 59
  • 117
Sahil Doshi
  • 621
  • 1
  • 9
  • 15
  • Possible duplicate of https://stackoverflow.com/q/44018443/1509264 or https://stackoverflow.com/q/37659643/1509264 – MT0 Sep 19 '17 at 14:48
  • The real question: why are you storing timestamps in a `VARCHAR` column? –  Sep 19 '17 at 15:20
  • I agree to it completely but it is not in my control and I can't change it – Sahil Doshi Sep 20 '17 at 06:01

1 Answers1

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.

prashant sugara
  • 321
  • 1
  • 7