CODEWITHSUNDEEP
sql-servert-sql
nojetlag
nojetlag

Reputation: 753

Convert String to Date in T-SQL

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 :(

Upvotes: 0

Views: 1107

Answers (3)

Irwin M. Fletcher
Irwin M. Fletcher

Reputation: 2666

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.

Upvotes: 1

womp
womp

Reputation: 116987

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

Upvotes: 0

Avitus
Avitus

Reputation: 15968

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.

Upvotes: 1

Related Questions