Convert String to Date in T-SQL

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

Since you're variables are 11 characters long and a date formatted this way is only 10 characters, perhaps that 11th character is a nonprintable character that's throwing it off? — Agent_9191
– Agent_9191, Commented Dec 23, 2009 at 15:54

Irwin M. Fletcher · Accepted Answer · 2009-12-23 15:56:42Z

1

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.

answered Dec 23, 2009 at 15:56

Irwin M. Fletcher

2,6663 gold badges25 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nojetlag Over a year ago

Hi everybody, indeed there was something at the beginning of the string. I had tried LTRIM and RTRIM and assumed that there was nothing (and looking at the output I couldn't see anything as well, until now). But now I tried: SELECT @String = SUBSTRING([Flat File Source Error Output Column],2,CHARINDEX(',',[Flat File Source Error Output Column])-6) and it works :) Thanks a lot

Irwin M. Fletcher Over a year ago

The Trim functions can be misleading, they will not remove new lines

Avitus · Accepted Answer · 2009-12-23 15:56:02Z

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.

answered Dec 23, 2009 at 15:56

Avitus

16k6 gold badges47 silver badges54 bronze badges

Comments

womp · Accepted Answer · 2009-12-23 15:56:38Z

0

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

answered Dec 23, 2009 at 15:56

womp

117k27 gold badges240 silver badges271 bronze badges

Collectives™ on Stack Overflow

Convert String to Date in T-SQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related