Sql server to Parquet file date missmatch using ADF copy data activity

Question

Sql server to Parquet file date missmatch using ADF copy data activity

gajanan choure 56

Hi ,

I am doing copy data activity in adf
Source - Sql server
Sink - parquet file
When doing copy Date field is changing
From 0001-01-01 is changing to 0001-01-03 in parquet.
It is incrementing the day by 2 in parquet file.

Why is it so ?

Thank you.

Ramarao Nagabairu 1 Reputation point

2022-06-14T17:55:09.527+00:00

Hi ShaikMaheer-MSFT, Thanks for the checking,

I am also facing the same issue, will revisit and get back here

Thanks,
Ram
Katarzyna Szmydki 0 Reputation points

2025-04-24T17:44:34.16+00:00

Hey,

Facing same issue. Anyone got it resolved? I don't think the accepted answer is correct.

Accepted answer

0 additional answers

Your answer

Ramarao Nagabairu 1 Reputation point

2022-06-14T17:55:09.527+00:00

Hi ShaikMaheer-MSFT, Thanks for the checking,

I am also facing the same issue, will revisit and get back here

Thanks,
Ram
Katarzyna Szmydki 0 Reputation points

2025-04-24T17:44:34.16+00:00

Hey,

Facing same issue. Anyone got it resolved? I don't think the accepted answer is correct.

Answer 1

ShaikMaheer-MSFT 38,521 Microsoft Employee

Hi @gajanan choure ,

Thank you for posting query in Microsoft Q&A Platform.

I tried to reproduce scenario. Its working fine for me. Data is not getting changed. Dates are getting copied properly.

In below example, I tried to copy data from SQL table to Parquet file in ADLS Gen2. Kindly check below screenshots.

So I don't see issue with Product or service. Kindly cross check your implementation with above screenshots and see if some where some configuration has to be corrected or make be your data validation logic may populating incorrect errors?

Hope this helps. Please let us know if any further queries.

--------------

Please consider hitting Accept Answer button. Accepted answers help community as well.

gajanan choure 56 Reputation points

2022-06-14T18:05:52.24+00:00

Hi Maheer ,
Thanks for the help .

Actually when in date year is less than 1582 we are getting issue .

Ex- 0001-01-01 is converted to 0001-01-03

When year is greater than 1582 when don't have issue date is not changing in that case .

Can you check taking date as 0001-01-01 or year less than 1582 ?

Thank you.
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2022-06-15T05:51:53.5+00:00

Hi @gajanan choure ,

Thanks for reply and sharing more details. I tried with dates 0001-01-01 and 1582 year and 1583 year. Still for me all works good. Kindly check below screenshots. Are we sure there is no other process involved that might be modifying dates in your case? May be give a try by creating a complete separate sample pipeline to check is dates are getting modified there as well? If yes, dates are getting modified then I feel we might need to have a deeper investigation to understand why it is. Feel free to raise support ticket in that case. Below link helps with steps to have support ticket.
https://learn.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request

My implementation screenshots:

Hope this helps.

--------
Please consider hitting Accept Answer button. Accepted answers help community as well.
gajanan choure 56 Reputation points

2022-06-15T06:01:53.54+00:00

In our case parquet file is taking INT96 as type for date column (doj in your case )

Is maybe because of that we are facing this issue?
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2022-06-15T06:13:18.257+00:00

Under copy activity mappings its INT96 only in my case too. Make sure your sink tab dataset don't import any schema. After file creation from copy activity, then to test data in it, create a separate dataset pointing to that file and preview data.

Hope this helps.

--------
Please consider marking it as Accepted Answer. Accepted answers help community as well.
gajanan choure 56 Reputation points

2022-06-16T03:35:29.167+00:00

Hi Maheer,

After creation of file from copy activity i have created dataset from it and did preview data . There we are getting correct date.

But when same parquet file i am reading in databricks and Reading data it is different date like 0001-01-03 in place of 0001-01-01.

When i am reading data by creating dataset in adf it is showing correct data.
But same when reading from databricks it is different.

Thank you
gajanan choure 56 Reputation points

2022-06-16T04:03:06.047+00:00

Hi Maheer ,

Now its working Thank you.

Actually in adf it is showing correct but while reading from databricks it was different because spark 3.0 changing to use Proleptic Gregorian calendar instead of hybrid Gregorian+julian calendar.

So by setting spark.sql.legacy.parquet.datetimeRebaseModeInRead property it is reading correctly.

Thank you for your help and support!
ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee

2022-06-16T07:06:46.403+00:00

Nice. Well tracked the issue. Thanks for sharing.

Share via

Sql server to Parquet file date missmatch using ADF copy data activity

0 additional answers

Your answer