Why is a date datatype replicated as datetime in MySQL target endpoint

[desmondchew]

We are replicating tables from Oracle 11g into MySQL 8.0. We found that tables with "date" datatype columns in Oracle are replicated as "datetime" in MySQL target endpoint.

According to the documentation here--> https://help.qlik.com/en-US/replicate/November2023/Content/Replicate/Main/MySQL/mysql_db_target_data...

It says that the date datatype should replicate as date. But it become datetime as default under the transformation.

[john_wang · Accepted solution]

Hello @desmondchew ,

There are several options to control the behavior. The easiest way is using a Global Transformation to convert datetime to date, as:

Convert data type
	for %.% with column % and data type DATETIME
	to data type DATE

Hope this helps.

John.

[desmondchew · Accepted solution]

Hi John,

Thank you I will try on the solution give. What I am actually asking is why is the datatype date replicate as "datetime" in MySQL when the document says it should be "date"?

Desmond

[SushilKumar]

Hello team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Regards,

Sushil Kumar

[john_wang · Accepted solution]

Hello Desmond @desmondchew ,

Good question.
This is how Replicate designed to make sure the DATE in Oracle can be hold completely in MySQL.

DATE in Oracle is stored in 7 bytes, the first 4 bytes presents date, the last 3 bytes presents time. Although the default format of DATE in Oracle shows as DD-MON-YY but it actually have time part. The DATE presents pretty long period, eg from BC 4712.01.01.

DATE in MySQL is stored in 3 bytes. And there are DATE and DATETIME datatypes in MySQL. From storage size, The match datatype DATE in Oracle should be map to DATETIME in MySQL, otherwise some values overflows in MySQL. So that depends on the precision you want. If you prefer the precision to date, then DATE type in MySQL is good enough. If you want precision set to seconds, then DATETIME type is needed in MySQL.

thanks,
John.