Date handling when profiling columns in Oracle - Cloud - 8.0

Talend Studio User Guide

Talend Big Data
Talend Big Data Platform
Talend Cloud
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Talend Studio
Design and Development
Last publication date
Available in...

Big Data Platform

Cloud API Services Platform

Cloud Big Data Platform

Cloud Data Fabric

Cloud Data Management Platform

Data Fabric

Data Management Platform

Data Services Platform

MDM Platform

Real-Time Big Data Platform

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.