-2

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.

emanuelbacigala
  • 11
  • 4

3 Answers3

0

Use try_convert() to find the problem values:

select DATE_COLUMN 
from dbo.table
where try_convert(date, DATE_COLUMN, 104) is null and
      DATE_COLUMN is not null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Your issue is not reproducible. The following test:

CREATE TABLE dt (DATE_COLUMN varchar(10));

INSERT INTO dt VALUES ('20.5.2018');

SELECT CONVERT(date, DATE_COLUMN, 104)  
FROM dt;

DROP TABLE dt;

returns 2018-05-20 and does not result in any error.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • I don't think the example value is the problem; thus we can't replicate the issue. The OP says themselves that their `CONVERT` on the string literal returns the date 2018-05-20. – Thom A Feb 18 '19 at 19:39
  • @Larnu true, the point of this exercise was to convince the OP that they are incorrect in thinking that the problem exists on every row of their data. And also in thinking that there is a difference between selecting the CONVERT() function with a table-supplied value as opposed to a string-literal value. Martin's comment suggested the right way to investigate, but OP is resistant to even trying it. – Tab Alleman Feb 18 '19 at 19:45
  • @TabAlleman I'm not at work atm and can't connect from home. I will definately try try_convert idea tommorow. However, the conversion didn't work on '20.5.2018' value, 12+ other values in the same format and even manually writing correct data in the source file didn't work, therefore I suggested that the problem is likely to be somwhere else. Will post try_convert results tomorrow. – emanuelbacigala Feb 18 '19 at 20:13
  • Ok, as you see in my answer, the conversion DOES work for me on the '20.5.2018' value, so maybe you made some typo or other mistake in your testing that doesn't show up in your question and prevented you from getting the right results. – Tab Alleman Feb 18 '19 at 20:24
0

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),'')
emanuelbacigala
  • 11
  • 4