The Ticks value for the datetime column must be between valid datetime ticks range - 0000-12-30 00:00:00

Question

The Ticks value for the datetime column must be between valid datetime ticks range - 0000-12-30 00:00:00

Mazhar Iqbal 30

Question asked elsewhere too.

We are using the OData Web Services to ingest data from Dynamics Business Central using Azure Data Factory.

Data is initially ingested into Parquet files in ADLS gen2. Then from there copied to Azure SQL db tables.

Since the weekend, this copy activity from ADLS gen2 to Azure sql db is failing with the following error. This isn't one Business Central table object but multiple.

"ErrorCode=ParquetDateTimeExceedLimit,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The Ticks value '-621357696000000000' for the datetime column must be between valid datetime ticks range -621355968000000000 and 2534022144000000000.,Source=Microsoft.DataTransfer.Richfile.ParquetTransferPlugin,'"

-621357696000000000 translates to "0000-12-30 00:00:00"

When looking at the parquet files we can see that indeed we have some duff datetime data .

Using Preview from the Dataset, the duff data is shown as (0001-01-01T00:00:00).

User's image

However, when viewing the data from a dataflow, the duff data is 0000-12-30 00:00:00 - the tick value causing the error

User's image

The source is Business Central data that are either not visible nor editable by end users.

I've tried some dynamic TabularTranslator mapping and a computed column in the target table to try and mitigate this but I still get the error.

ADF TabularTranslator

    {
        "source": {
            "name": "LastModifiedDateTime"
        },
        "sink": {
            "name": "str_LastModifiedDateTime"
        }
    },

SQL table

[str_LastModifiedDateTime] [varchar](50) NULL,  
[LastModifiedDateTime]  AS 
(TRY_CAST([str_LastModifiedDateTime] AS [datetime2])),

I'm now at a loss.
How can I build a different way to either ingest the data or read the data from parquet without these java code gregorian/julius calendar shenanigans getting in the way?


                "fileSystem": {
                    "value": "@dataset().containername",
                    "type": "Expression"
                }
            },
            "compressionCodec": "snappy"
        },
        "schema": []
    },
    "type": "Microsoft.DataFactory/factories/datasets"
}

Amira Bedhiafi 41,641 Reputation points MVP Volunteer Moderator

2024-09-09T21:28:24.8433333+00:00
Before writing to Azure SQL DB, you can introduce a Data Flow in ADF to clean or transform the invalid datetime values in the Parquet files. You can use the Derived Column transformation to replace invalid dates with null values or a default valid date. Here’s a general approach:

Add a condition to check if the datetime value is outside the valid range

Use a replacement value, such as a NULL, or a valid date like 1900-01-01

iif(LastModifiedDateTime < toTimestamp('1753-01-01') || LastModifiedDateTime > toTimestamp('9999-12-31'), NULL, LastModifiedDateTime)

If you want to remove rows containing invalid dates altogether, you can apply a filter transformation in the data flow to exclude these records:

Use a filter transformation to check the tick range of the datetime column

Ensure that only rows with valid datetime values are passed to the sink

LastModifiedDateTime >= toTimestamp('1753-01-01') && LastModifiedDateTime <= toTimestamp('9999-12-31')

Since you already tried using TRY_CAST in your SQL script, you might want to ensure that all invalid datetime values in the Parquet files are mapped to a string column in Azure SQL DB. Once in SQL, you can convert the string values back into a datetime2 type using the TRY_CAST function, and handle any remaining invalid dates.
Mazhar Iqbal 30 Reputation points

2024-09-10T08:50:21.9633333+00:00

Thanks for the suggestion but I thought of this already. However, this approach will not work for me.

I need a dynamic approach. The problem affects different parquet files. Some will have date/datetime columns some will not. These date/datetime columns will have different names across these files.
Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-10T20:52:53.8466667+00:00
Hi @Mazhar Iqbal

Thank you for reaching out to MS Q&A.

It seems like the error you're encountering is related to a known limitation involving the difference between the Julian and Gregorian calendars. The datetime value 0001-01-01 00:00:00 falls outside the valid range for Parquet processing in Azure Data Factory, which is causing the issue.

ErrorCode=ParquetDateTimeExceedLimit, Message=The Ticks value '-621357696000000000' for the datetime column must be between valid datetime ticks range -621355968000000000 and 2534022144000000000.

This error arises from datetime values outside the valid ticks range (e.g., 0001-01-01 00:00:00), which is due to the difference between the Julian and Gregorian calendar dates. You can learn more about this here.

Since this is a known limitation, you can use a derived column in Azure Data Factory to modify the datetime values in your Parquet files to valid values within the Gregorian calendar.

Alternatively, you can use Apache Spark within a notebook to handle reading and writing Parquet files, which may help avoid this limitation.
Here's a helpful guide: Read & Write Parquet Files using Apache Spark in Azure Synapse.

Let me know if this workaround helps, or if you need further assistance!

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Mazhar Iqbal 30 Reputation points

2024-09-11T15:06:50.1566667+00:00

Thanks, but this answer is similar to the other one. This solution will not work me,
Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-13T16:23:13.9933333+00:00

@Mazhar Iqbal Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer accepted by question author

2 additional answers

Your answer

Amira Bedhiafi 41,641 Reputation points MVP Volunteer Moderator

2024-09-09T21:28:24.8433333+00:00

Before writing to Azure SQL DB, you can introduce a Data Flow in ADF to clean or transform the invalid datetime values in the Parquet files. You can use the Derived Column transformation to replace invalid dates with null values or a default valid date. Here’s a general approach:

Add a condition to check if the datetime value is outside the valid range

Use a replacement value, such as a NULL, or a valid date like 1900-01-01

iif(LastModifiedDateTime < toTimestamp('1753-01-01') || LastModifiedDateTime > toTimestamp('9999-12-31'), NULL, LastModifiedDateTime)

If you want to remove rows containing invalid dates altogether, you can apply a filter transformation in the data flow to exclude these records:

Use a filter transformation to check the tick range of the datetime column

Ensure that only rows with valid datetime values are passed to the sink

LastModifiedDateTime >= toTimestamp('1753-01-01') && LastModifiedDateTime <= toTimestamp('9999-12-31')

Since you already tried using TRY_CAST in your SQL script, you might want to ensure that all invalid datetime values in the Parquet files are mapped to a string column in Azure SQL DB. Once in SQL, you can convert the string values back into a datetime2 type using the TRY_CAST function, and handle any remaining invalid dates.
Mazhar Iqbal 30 Reputation points

2024-09-10T08:50:21.9633333+00:00

Thanks for the suggestion but I thought of this already. However, this approach will not work for me.

I need a dynamic approach. The problem affects different parquet files. Some will have date/datetime columns some will not. These date/datetime columns will have different names across these files.
Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-10T20:52:53.8466667+00:00

Hi @Mazhar Iqbal

Thank you for reaching out to MS Q&A.

It seems like the error you're encountering is related to a known limitation involving the difference between the Julian and Gregorian calendars. The datetime value 0001-01-01 00:00:00 falls outside the valid range for Parquet processing in Azure Data Factory, which is causing the issue.

ErrorCode=ParquetDateTimeExceedLimit, Message=The Ticks value '-621357696000000000' for the datetime column must be between valid datetime ticks range -621355968000000000 and 2534022144000000000.

This error arises from datetime values outside the valid ticks range (e.g., 0001-01-01 00:00:00), which is due to the difference between the Julian and Gregorian calendar dates. You can learn more about this here.

Since this is a known limitation, you can use a derived column in Azure Data Factory to modify the datetime values in your Parquet files to valid values within the Gregorian calendar.

Alternatively, you can use Apache Spark within a notebook to handle reading and writing Parquet files, which may help avoid this limitation.
Here's a helpful guide: Read & Write Parquet Files using Apache Spark in Azure Synapse.

Let me know if this workaround helps, or if you need further assistance!

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Mazhar Iqbal 30 Reputation points

2024-09-11T15:06:50.1566667+00:00

Thanks, but this answer is similar to the other one. This solution will not work me,
Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-13T16:23:13.9933333+00:00

@Mazhar Iqbal Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Chris Smith-Kirk 80

We had a similar problem and it started last weekend.

Microsoft advised:

Add the following JSON to our parquet integration data set: "useParquetV2": true

e.g.

            "compressionCodec": "snappy",
            "useParquetV2": true
        },

Root cause:

Our recent upgrade of underlying parquet library to address CVEs seems to be the culprit. So Parquet V1 and Spark use the same Parquet-MR Java library for reading/writing parquet files.

Spark 3.0 introduced a breaking change where the default system calendar was switched to Proleptic Gregorian calendar, but INT96 Timestamp requires Julian calendar, so for timestamp values prior to 1900-01-01T00:00:00Z, that are ambiguous between these two calendars, Parquet-MR automatically shifts the value in order to rebase from Proleptic Gregorian calendar to Julian calendar w.r.t. calendar differences. It impacts ADF's Parquet v1 as well.

Mazhar Iqbal 30 Reputation points

2024-09-19T08:09:33.5733333+00:00

Thanks Chris.
Just what I suspected. Something MS did that broke this.

Do you have a link that describes this or is it just an email from Microsoft support?
Scholten, Femke 0 Reputation points

2024-09-23T06:41:01.1366667+00:00

Thanks for posting your solution, we are encountering the same issue since last week causing our ADF pipelines to fail. Can you explain where to adjust this setting in ADF?

Thanks
Mazhar Iqbal 30 Reputation points

2024-09-23T09:39:50.4366667+00:00
On the left hand side, under datasets, click on your parquet dataset.

Then click on "view json code represenattion..."

Scroll down until you reach "compression codec" and add the following. `

"useParquetV2": true

Click OK
Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-24T09:33:49.05+00:00

@Chris Smith-Kirk @mazhar iqbal Thank you for sharing the details which will be beneficial to other community members on the similar issue.
Mark Selley 0 Reputation points

2024-09-26T20:05:04.98+00:00

This solved our issues too, but we would like to know more information about "useparquetV2" as it appears to be completely undocumented.
Are they able to provide you with any additional information about this setting?

Answer 2

Hi @Mazhar Iqbal
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Issue: We are using the OData Web Services to ingest data from Dynamics Business Central using Azure Data Factory.

Data is initially ingested into Parquet files in ADLS gen2. Then from there copied to Azure SQL db tables.

Since the weekend, this copy activity from ADLS gen2 to Azure sql db is failing with the following error. This isn't one Business Central table object but multiple.

"ErrorCode=ParquetDateTimeExceedLimit,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The Ticks value '-621357696000000000' for the datetime column must be between valid datetime ticks range -621355968000000000 and 2534022144000000000.,Source=Microsoft.DataTransfer.Richfile.ParquetTransferPlugin,'"

-621357696000000000 translates to "0000-12-30 00:00:00"

When looking at the parquet files we can see that indeed we have some duff datetime data .

Using Preview from the Dataset, the duff data is shown as (0001-01-01T00:00:00).

User's image

However, when viewing the data from a dataflow, the duff data is 0000-12-30 00:00:00 - the tick value causing the error

User's image

The source is Business Central data that are either not visible nor editable by end users.

I've tried some dynamic TabularTranslator mapping and a computed column in the target table to try and mitigate this but I still get the error.

ADF TabularTranslator

JSONAI ConvertCopy

    {
        "source": {
            "name": "LastModifiedDateTime"
        },
        "sink": {
            "name": "str_LastModifiedDateTime"
        }
    },

[str_LastModifiedDateTime] [varchar](50) NULL,  
[LastModifiedDateTime]  AS 
(TRY_CAST([str_LastModifiedDateTime] AS [datetime2])),

I'm now at a loss. How can I build a different way to either ingest the data or read the data from parquet without these java code gregorian/julius calendar shenanigans getting in the way?


                "fileSystem": {
                    "value": "@dataset().containername",
                    "type": "Expression"
                }
            },
            "compressionCodec": "snappy"
        },
        "schema": []
    },
    "type": "Microsoft.DataFactory/factories/datasets"

Solution: The solution I ended up with was adding a another Copy Data activity, immediately before the one that reads data from the Parquet to the SQL table, this one is to copy data from the Parquet file and create a new CSV file. And then amended the existing Copy Data activity to read data from the CSV instead of the Parquet.

Strangely enough, the duff 0000-12-30 00:00:00 transformed itself to the valid 0001-01-01 00:00:00 timestamp in the CSV without any intervention from me.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-12T15:14:39.2033333+00:00

@Mazhar Iqbal Following up to see if the above answer was helpful. please do click Accept Answer and Yes for "was this answer helpful", as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Answer 3

Mazhar Iqbal 30

The solution I ended up with was adding a another Copy Data activity, immediately before the one that reads data from the Parquet to the SQL table, this one is to copy data from the Parquet file and create a new CSV file. And then amended the existing Copy Data activity to read data from the CSV instead of the Parquet.

Strangely enough, the duff 0000-12-30 00:00:00 transformed itself to the valid 0001-01-01 00:00:00 timestamp in the CSV without any intervention from me.

Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-11T15:44:41.76+00:00

@Mazhar Iqbal Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer
Smaran Thoomu 35,125 Reputation points Microsoft External Staff Moderator

2024-09-16T15:41:49.2733333+00:00

@Mazhar Iqbal Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

The Ticks value for the datetime column must be between valid datetime ticks range - 0000-12-30 00:00:00

2 additional answers

Your answer