topic Re: Qlik replicate DATE issue with JD in Qlik Replicate

Qlik replicate DATE issue with JD

gopikrishnan06 — Thu, 23 Oct 2025 17:58:43 GMT

Hi we are using Oracle as source and Databricks delta lakehouse as target.

In oracle we used DATE datatype so we 01-jan-2012. But in Databricks using the same DATE datype am getting julian Date as 4712 -01-01..

Kindly advise i need as same like as Oracle Date format.

Re: Qlik replicate DATE issue with JD

gopikrishnan06 — Thu, 23 Oct 2025 17:59:26 GMT

4712-01-01T00:00:00.000+00:00 as SAMPLE

Re: Qlik replicate DATE issue with JD

Dana_Baldwin — Thu, 23 Oct 2025 18:07:04 GMT

Hi @gopikrishnan06

Please try adjusting the setting "expose number as" on the advanced tab of the source endpoint to "Varchar". This should retain the formatting from the source database.

Thanks,

Dana

Re: Qlik replicate DATE issue with JD

DesmondWOO — Fri, 24 Oct 2025 02:20:51 GMT

Hi @gopikrishnan06 ,

Did you create the target table using Qlik Replicate or manually? According to the user guide, Oracle's DATE datatype should map to Datarbricks's TIMESTAMP datatype.

Regards,
Desmond

Re: Qlik replicate DATE issue with JD

gopikrishnan06 — Fri, 24 Oct 2025 08:48:54 GMT

Hi Dana,

Already we set it to Source side as numeric (38,10) as per support team advise.

Here in oracle as DATE so in databricks it auto convert it to DATETIME.. when i check in databricks it showing as timestamp..

But how to convert the JD date in normal time

As of now am getting 4712-01-01 need to get as like as 01-JAN-12

Re: Qlik replicate DATE issue with JD

gopikrishnan06 — Fri, 24 Oct 2025 12:02:09 GMT

hi Dan,

I change the expose number from numeric 38,10 into Varchar. Still it showing as Julian date alone. In Databricks it showing as string. Kindly help on this..

Databricks 4712-01-01 as julian date.. But in Oracle as DATE datatype as 01-JAN-12..

Re: Qlik replicate DATE issue with JD

gopikrishnan06 — Mon, 03 Nov 2025 14:00:38 GMT

Hi All,

Raised concern with the PS . Still not getting any solution. Pasting here for wider audiences.

Am using Oracle Data as Source. In Oracle am getting "1/1/4712 12:00:00 AM" and in target am using Databricks delta lakehouse.

In Databricks am getting results as "4712-01-01"..this as not correct format i need like this "2012-01-01".

here in Oracle other date format getting as Proper."5/25/2010 12:00:00 AM" am getting results as "2010-05-25".

Only issue with "1/1/4712 12:00:00 AM"

If any solution please let me know.. It will helpful.

Re: Qlik replicate DATE issue with JD

john_wang — Tue, 04 Nov 2025 08:21:24 GMT

Hello @gopikrishnan06 ,

I wasn’t able to reproduce the behavior in my lab. I’m running Replicate 2025.5.0.308 with Oracle 19c as the source. I prepared three rows (2, 3, 8 ) for the Full Load, and after the load completed, inserted another three rows (12, 13, 18). All rows were successfully replicated to the target.

Whatever the DT maps to DATE, or TIMESTAMP in Databricks. for example:

CREATE TABLE johnw_qmi_unitycatalog.default.testdt (
ID DECIMAL(38,0) NOT NULL,
NAME VARCHAR(20),
DT DATE,
CONSTRAINT `testdt_pk` PRIMARY KEY (`ID`) RELY)
USING delta
TBLPROPERTIES (
'delta.autoOptimize.optimizeWrite' = 'true',
'delta.checkpoint.writeStatsAsJson' = 'false',
'delta.checkpoint.writeStatsAsStruct' = 'true',
'delta.enableDeletionVectors' = 'true',
'delta.enableRowTracking' = 'true',
'delta.feature.appendOnly' = 'supported',
'delta.feature.deletionVectors' = 'supported',
'delta.feature.domainMetadata' = 'supported',
'delta.feature.invariants' = 'supported',
'delta.feature.rowTracking' = 'supported',
'delta.minReaderVersion' = '3',
'delta.minWriterVersion' = '7',
'delta.parquet.compression.codec' = 'zstd')

Regards,

John.

Re: Qlik replicate DATE issue with JD

gopikrishnan06 — Tue, 04 Nov 2025 09:32:46 GMT

Thank John_wang.

Here in Databricks what ever the date format come as 2002 , 2020 and 2024 and 2008 those date are coming perfect in the Databricks.

only issue with the "1/1/4712 12:00:00 AM" date format what ever the steps and case tried with the global parameter it not working..

Uploaded the file check for the AO and AW column.. Kindly check and fix it

Re: Qlik replicate DATE issue with JD

john_wang — Tue, 04 Nov 2025 15:18:17 GMT

Hello @gopikrishnan06 ,

Would you please check the hex value of the column, eg:

select dt, dump(dt) from testdt where id=3;

Regards,

John.

Re: Qlik replicate DATE issue with JD

gopikrishnan06 — Wed, 05 Nov 2025 12:04:54 GMT

hi John, Issue fixed thank you

Re: Qlik replicate DATE issue with JD

john_wang — Wed, 19 Nov 2025 09:47:28 GMT

Thank you for your support @gopikrishnan06 !