After importing date strings in dd.(m)m.yyyy format from .csv I don't seem to be able to convert them into dates.
Using CONVERT (date, DATE_COLUMN, 104) causes an error:
Conversion failed when converting date and/or time from character string.
However, if I try to convert values that I copied from the column selection output (CONVERT (date, '20.5.2018', 104)), the code works fine for each single value in the column.
Trimming the strings didn't work. I have also tried manually rewriting the date string in the source file, but the result is the same.
To summarize (using 1 example value):
select CONVERT(date, DATE_COLUMN, 104)
from dbo.table
returns:
Conversion failed when converting date and/or time from character string.
select DATE_COLUMN
from dbo.table
returns: 20.5.2018
select CONVERT(date,'20.5.2018', 104)
returns: '2018-5-20'
I would expect SQL Server to treat each convert the same way. While there is a workaround (splitting the string into dateparts and combining them into date), I don't understand why the conversion fails in the first place. Any help appreciated.
The problem was caused by carriage return being imported to every row along with the string. The row delimiter of the flat file connection was set to {LF}, however some of the source flat files used {CR}{LF} delimiter, therefore, the {CR} character was being imported to the column.
Since I had tried trimming the values and it didn't work, my conclusion was, that the 'invisible' characters were all taken care of. To be honest, it have never even occurred to me, that it was possible to import carriage return into a column.
Since I could not alter the source files, and I'm not sure how to set dynamic delimiter for import, I solved the problem by removing {CR} with:
REPLACE(DATE_COLUMN,char(13),'')