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

author
Talend Documentation Team
EnrichVersion
6.4
6.3
6.2
6.1
6.0
EnrichProdName
Talend Open Studio for Data Quality
Talend Data Fabric
Talend Real-Time Big Data Platform
Talend Open Studio for MDM
Talend Data Services Platform
Talend MDM Platform
Talend Big Data Platform
Talend Data Management Platform
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.