Date format issue

suvbin — Tue, 14 May 2024 02:45:15 GMT

Hi Team,

Facing date issue. In the source the date column values are 04/30/2023, 04/23/2023...... , But in the target we are getting in this way Target : 1970-01-01, 1970-02-05.............

source : SAPHANA

Target : snowflake

Its a full load task

Request your help.

Thanks.

Re: Date format issue

Heinvandenheuvel — Tue, 14 May 2024 03:59:08 GMT

[edited]

You need to provide more details. What is the source data-type, target data-type?

Are the source/target you show as examples random examples from non-correlated rows or is 04/30/2023 --> 1970-01-01, and 04/23/2023 --> 1970-02-05 for the same source rows?

"04/30/2023" May look like a date to some folks, but is just a string of gibberish to me and perhaps to Replicate as well, as it expects YYYY-MM-DD.

What if you add a text column and fill it with that 'date' value? What does it look like as a string. If you see mm/dd/yyyy then I suspect you need a transformation on your main column parsing out the mm, dd and yyyy perhaps with substring if leading zeros are exposes and glueing them back together with dashes as input to date().

1970-01-01 is of course highly suspect as 1 January 1970 00:00:00 UTC is the Unix Epoch start time.

I suspect your source date is in perhaps an INTEGER number off days since a certain SAP base date treated as seconds since Epoch.

edit: Check out https://www.epochconverter.com/ - a vary handy site with many transformations. For yucks; if you fill in 20230423 (interpreted as seconds since 1/1/1907 you get Sunday, August 23, 1970.

edit: Google for sap hana internal data format suggests YYYYMMDD (number!) and there are several hits asking how to convert to mm/dd/yyyy string, suggesting that is NOT a normal sap date.

topic Date format issue in Qlik Replicate

Date format issue

Re: Date format issue