Convert String to Date

Question

I try to convert a string into a date in t-sql. However get results that I can't explain.

DECLARE @String as char(11)
DECLARE @TString as char(11)

SELECT @String = SUBSTRING([Flat File Source Error Output Column],1,CHARINDEX(',',[Flat File Source Error Output Column])-6)
FROM [ERROR].[Import_V2X]

SELECT @TString = '12/18/2009'

-- Check content before conversion
SELECT @TString as 'CheckTString'
SELECT @String as 'CheckString'

-- Convert the strings to date       
SELECT CONVERT(date,@TString,101) as 'ConvertSuccess'

SELECT CONVERT(date,@String,101) as 'ConvertFails'

[Flat File Source Error Output Column] is defined as text in the table

This gives me the following result:

CheckTString
------------
12/18/2009 

(1 row(s) affected)

CheckString
-----------

12/18/2009

(1 row(s) affected)

ConvertSuccess
--------------
2009-12-18

(1 row(s) affected)

ConvertFails
------------
Msg 241, Level 16, State 1, Line 16
Conversion failed when converting date and/or time from character string.

Anybody can explain me where the problem is or comes from ? For me the strings look exactly the same :(

Answer 1

If I had to guess it's because you're imported string has a non-visible character at the end of the string that doesn't allow it to convert. Your variable is char(11) but the string '12/18/2009' is only 10 characters long so that leaves room for 1 more character at the end.

Answer 2

Looks like @CheckString potentially has a newline character at the beginning.

Answer 3

By the look of your output you have a line feed in the checkstring variable. If this is not just a copy and paste error in the question, that will cause the error that you are describing. See below

DECLARE @TString as char(11)


SELECT @TString = '
12/18/2009'

-- Check content before conversion
SELECT @TString as 'CheckTString'

-- Convert the strings to date       
SELECT CONVERT(date,@TString,101) as 'ConvertFails'

Gives the following results.

(1 row(s) affected)
Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.

Answer 4

For the last line try

SELECT CONVERT(date, LTRIM(RTRIM(@String)) , 101) as 'ConvertFails'

and see if that works