Date column recognized as data type but it set as NULL to a specific format

0
votes

To put in context check it out here: Need to import a DATE column in excel to a SQL table using SSIS

Another issue I found a few moments ago. If SSIS set one column of date as DATE datatype, dates like 19 Apr 2017 will disappear (will turn NULL in the original table)... but not those with 24/nov/16 format. This happens when we load the original table (before the staging table)... The case which never gives problems is when SSIS set the column of date as Unicode string [DT_WSTR] (255). This is driving me mad! How to overcome this new issue? Cannot change the original excel file, neither to force to change the datatype in SSIS for the date column (from DATE to Unicode String). Thanks! How to overcome this new issue?

EXCEL FILE

date 
 19 Apr 2017
 n/a 
 07/nov/13

WHAT we see in the original table

date
 NULL
 n/a
 2013-11-07

what it should appear:

date 
 19 Apr 2017 or something that shows this info (format does not matter)
 n/a
 2013-11-07
1
ssisssis-2012
if I could made a slight change to that column in excel, I have two options: put the GENERAL data type in that column DATE, OR .... move some dates like the format 19 Apr 2017 in the first 8 rows... and in the last case the SSIS will assume a [DT_WSTR] (255) data type!!! otherwise it accepts the date (in the case the first 8 rows have more dates than 'n/a'... ) , which means that all dates with the format 19 apr 2017 WILL go NULL in the original table (from the excel to the original table) - graphene
even I change the excel file as I told in the previous comment... SSIS insist to set the column DATE as date datatype... in this way I i will loose the 19 Apr 2017 date format to NULL. It cannot happen! - graphene
i've seen someone recommend select CDate([col]) from [Sheet1$] as a solution to this, but I am not sure it works - KeithL
No, Keith. It retrieves only correctly the dates with format mm/dd/yy or similar to this. But the format 19 Apr 2017 it says "Unable to read data" :( . - graphene

1 Answers

1
votes

It seems that If I write a sql comand like select FORMAT([LoginDate],'YYYY-MM-DD') as F1 it works.