Qlik replicate DATE issue with JD

[gopikrishnan06]

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.

[gopikrishnan06]

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

[Dana_Baldwin]

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

[DesmondWOO]

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

[gopikrishnan06]

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

[gopikrishnan06]

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..

[gopikrishnan06]

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.

[john_wang]

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.

[gopikrishnan06]

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

[john_wang · Accepted solution]

Hello @gopikrishnan06 ,

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

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

Regards,

John.