Restrict ADF converting Null data from source date field returning 1900-01-01 in target column

Question

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?

Answer 1

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?

Answer 2

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.

Answer 3

Thanks @Nandan Hegde .
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

Answer 4

Hello @HimanshuSinha-msft ,

I have same issue with ADF v2. Source is ADF and Sink is Azure SQL DB.

I already tried to accept data in TABLE TYPE (FieldName01(VARCHAR(max)) and then CAST(FieldName01 as DATE) in Stored Procedure.

Thanks in advance for anyone who come across this and able to shed some light on this.