Date handling when profiling columns in Oracle - 7.3

Talend Open Studio User Guide

Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Studio
Design and Development
Last publication date
Available in...

Open Studio for Data Quality

When profiling a column of a Date type in an Oracle database using Pattern Frequency Statistics, the result of the column analysis is 99-AAA-99 with the SQL engine, and not 9999-99-99 99:99:99.9 as expected. If you run the analysis with the Java engine, you will get 9999-99-99 99:99:99.9.

In Oracle, dates are stored as numbers. Talend uses the Cast function with Pattern Frequency Statistics. When you run the analysis with the SQL engine, the query casts the Date type to a text type and the original date format is changed from 9999-99-99 99:99:99 to 99-AAA-99.

As all dates are date objects in the Date column, dates will always be displayed using one single format in Oracle (99-AAA-99) and using another single format such as 9999-99-99 99:99:99.9 in non-Oracle and non-SQL cases. This is why no data quality issue can be found using this indicator. It is then not advisable to use Pattern Frequency Statistics on a Date type column in databases.