Qlik Replicate: How to convert date datatype in the format DD-MMM-YY in Kafka

[john_wang]

In Oracle databases, the date is in the format DD-MMM-YY (eg: 21-May-22), while Qlik Replicate from Oracle to Kafka, the default format in Kafka is 2022-05-21 00:00:00 (even source & destination representing the same date but their format differs).

        In SQLite Data Function there are some types include:

• YYYY-MM-DD
• YYYY-MM-DD HH:MM
• YYYY-MM-DD HH:MM:SS
• YYYY-MM-DD HH:MM:SS.SSS
• YYYY-MM-DDTHH:MM
• YYYY-MM-DDTHH:MM:SS
• YYYY-MM-DDTHH:MM:SS.SSS
• HH:MM
• HH:MM:SS
• HH:MM:SS.SSS
• now
• DDDDDDDDDD

The pre-defined format DD-MMM-YY does not exist.

Is there any way to keep the same source format ( DD-MMM-YY ) in the destination Kafka topic as well?

Environment:

Qlik Replicate  

 

Detailed information

1. In Oracle DB the table and rows sample like:
   
   

   create table testdt (id integer not null primary key , name char(20), dt date);
   
   insert into testdt values (2,'test',sysdate);commit;
   insert into testdt values (5,'test5',to_date('20220521','YYYYMMDD'));commit;​

   
   
   
2. In Replicate transformation define a new column "DT2" with data type STRING(5) the expression is:
   
   

   strftime('%d',$DT)||'-'||substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3*strftime('%m', $DT), -3)||'-'||substr(strftime('%Y',$DT),3,2)​

   (Take note the format strings are case sensitive, eg "%m" &"&M" are different.)

   
   
   
   
   
   
3. In Kafka the  column DT2 is in format DD-MMM-YY.  (The column "DT" can be removed, it's used for Comparison to the original date column DT.)
   
   

Related Content 

Support case: #00021716