Hello All, I am not sure, whether this was already asked, I did a search and didn't find any suitable replies related to this.
ADF copy data is returning '1900-01-01' by default to a NULL DateTime field when moving the data from Source to target table while doing the Polybase copy. Both of my source/target are Azure SQL DW tables
I couldn't find anything to change such settings while creating the copy command pipeline in ADF. Any pointers to restrict ADF from doing the automatic conversion?
Hello @karthikshanth ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
Hello @karthikshanth ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
Yeah, I figured out no alternative ways. I went with an update statement post finishing the ADF polybase loads. I know, it's not the best way, but I have left with no other choice.
Plus, The simple copy wouldn't be an obvious choice, since I m dealing with huge data which takes a longer time to load the data with the option.
Hey @karthikshanth ,
Based on my knowledge, unfortunately there is no alternative for it.
Whenever you use polybase functionality, ADF provides the default value for NULL values like 0 for int and 1900-01-01 for datetime.
There are 2 ways:
1) either keep it a simple copy activity so that null value would be retained
2) Load into a staging table and then while loading into destination convert the values back to null
@HimanshuSinha-MSFT : Any thoughts?
A not pretty solution would be to convert them to strings first, then load into a staging table. Convert them back through some sort of merge.
Thanks @NandanHegde-7720 .
Hello @karthikshanth,
Great question and I did tried to repro the issue and I was able to do that .
Took some dummy data .
--DROP TABLE DATETEST
CREATE TABLE DATETEST
(
ID INT ,
SOMEDATE DATETIME NULL
)
SELECT GETUTCDATE()
INSERT INTO DATETEST VALUES (1,'2021-03-16 21:51:35.907')
INSERT INTO DATETEST(ID) VALUES (2)
--DROP TABLE DATETEST_DESTINATION
CREATE TABLE DATETEST_DESTINATION
(
ID INT ,
SOMEDATE DATETIME NULL
).
Please uncheck "Use Type default" and that should do the trick . I am sharing the snapshot for reference below .
let me know how it goes .
Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
15 people are following this question.
