Why do I get 99-AAA-99 when profiling Date columns in Oracle?

EnrichVersion
6.4
6.3
6.2
6.1
6.0
5.6
EnrichProdName
Talend Data Fabric
Talend Real-Time Big Data Platform
Talend Data Management Platform
Talend Open Studio for MDM
Talend MDM Platform
Talend Data Services Platform
Talend Big Data Platform
Talend Open Studio for Data Quality
task
Data Quality and Preparation > Profiling data
EnrichPlatform
Talend Studio

Date handling when profiling columns in Oracle

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.