CODEWITHSUNDEEP
sqloracle-databaseetl
Sahil Doshi
Sahil Doshi

Reputation: 651

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

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?

Upvotes: 0

Views: 791

Answers (1)

prashant sugara
prashant sugara

Reputation: 321

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.

Upvotes: 1

Related Questions