Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,609 questions
Update table by Hour - The data types time and datetime2 are incompatible
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(275.2, 31%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
BGO
46
Reputation points
Hello,
I'm trying to update a table in Synapse by LocalDate and Hour columns. The LocalDate defined as a DATE and Hour as a TIME(7) in the database. There is a problem with the Hour column as it's coming from the source as a string.
Here is an example of the source data:
I added Derived Column and tried to convert the string to the timestamp
This is how the sink looks like:
but I'm getting the following error:
Job failed due to reason: at Sink 'sink1': The data types time and datetime2 are incompatible in the equal to operator.
How can I convert the Hour string to the right time format?
Azure Data Factory
{count} votes
-
AnnuKumari-MSFT 34,551 Reputation points Microsoft Employee Moderator2023-06-29T17:00:58.43+00:00 Hi BGO ,
Thankyou for using Microsoft Q&A platform and thanks for posting your question here.
It seems that you are getting datatype incompatibility issue while using mapping dataflow.
Since the source has time datatype for 'hour' column , it should show the same in dataflow as well. Could you please import schema for the source dataset and see if it is still coming as a string?
Kindly help me with some additional information:
What is your sink datastore? Does the 'Hour' column in sink has datetime datatype?
Additionally, why are you using localDate and Hour as the key column for performing upsert. A Key Column should be a unique column identifier which makes each record unique in the table , be it primary key, or composite key(combination of two keys)
Hope to get your response with the required details. Thankyou
-
AnnuKumari-MSFT 34,551 Reputation points Microsoft Employee Moderator
2023-07-03T06:25:33.63+00:00 Hi BGO ,
We haven't heard from you after the last response. Did you get a chance to take a look? Kindly revert back with the required details. Thankyou
-
BGO 46 Reputation points
2023-07-04T16:20:06.1066667+00:00 The LocalDate and Hour are the composite key. That's why I'm using both columns for performing upsert.
As I mentioned the Hour column is coming as a string from the source. In the sink datastore (Synapse db) the Hour column defined as TIME(7). There is no issue with the LocalDate column. I just need to convert the source Hour column to the Time datatype so it can compare the source value with the table's value. There is only a toTimestamp function I could find but I don't see anything like "toTime".
Btw if I'm just performing an insert, the string Hour column is getting inserted to the table as time without any issue.
-
AnnuKumari-MSFT 34,551 Reputation points Microsoft Employee Moderator
2023-08-02T08:23:08+00:00 BGO ,
Key column expects unique identity column name. Kindly try using hashing technique to compare the data between source and sink and update/insert the new data, for more details, kindly check this video: https://www.youtube.com/watch?v=i2PkwNqxj1E&t=592s
Sign in to comment
Sign in to answer