need help converting varchar to datetime 1

[pandatime]

Hi,

I have a flat file that has dates in the following format:

2010-05-12 12:32:11, for example

However I am seeing the following inconsistencies when I try to convert this format to a datetime:

select [date] from myTable -- 2010-05-12 12:32:11

This works:

select convert(datetime, '2010-05-12 12:32:11')
-- 2010-05-12 12:32:11.000

This doesn't work, why?:

select convert(datetime, [date]) from myTable

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

Thanks

 

[pandatime]

I should mention, [date] is currently stored as a varchar(50).

And the problem arose when attempting to insert this data to another table. But I can't even convert it to a datetime as a preliminary step.

 

[gmmastros]

It is almost certainly bad data. Try this...

[tt]
Select [date], IsDate([date]) from myTable where IsDate([date]) = 0[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

yep i did that already. clearly it is not a recognizable datetime format. my question is how to deal with it?

also how come this works???

select convert(datetime, '2010-05-12 12:32:11')
-- 2010-05-12 12:32:11.000

i pulled that date directly from the table.

select max(len(ltrim(rtrim([date])))) from myTable -- 20

this also works:

select convert(datetime, '2010-05-12 12:32:11 ')
-- 2010-05-12 12:32:11.000

 

[markros]

I agree with George - it is probably the dates which can not be converted throwing this error. You can filter them out with ISDATE([date]) = 0

PluralSight Learning Library

 

[pandatime]

I mention that last example because it does look like there's a trailing space.

 

[pandatime]

nevermind all my imported columns have this trailing "space", that is a problem

 

[gmmastros]

Trailing spaces should not be a problem, but other trailing characters could be. Ex:

select convert(datetime, '2010-05-12 12:32:11' + ' ')
select convert(datetime, '2010-05-12 12:32:11' + Char(13))
select convert(datetime, '2010-05-12 12:32:11' + Char(10))
select convert(datetime, '2010-05-12 12:32:11' + Char(9))

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

yep, it was a char(9)

thanks