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.