Date handling when profiling columns in Oracle - 7.1

Talend Data Management Platform Studio User Guide

EnrichVersion
7.1
EnrichProdName
Talend Data Management Platform
EnrichPlatform
Talend Studio
task
Design and Development
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.