Conversion failed when converting datetime from character string

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I guess it depends on what you want to do with a date that's a zero-length string. What does that represent, in terms of use/business logic?

If it means "we don't know the date", then use NullIf to turn it into a Null.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

As Gsquared said. "It depends"

But if you just want to avoid the error message then use NULLIF:

declare @test-2 varchar(30)

Set @test-2 = ''

--Set @test-2 = '2009 12 17 22 31 00 000'

set @test-2=nullif(@test,'')

Select

DATEADD(hh, -5, CONVERT(DATETIME, SUBSTRING(@test,1,4) + '-' + SUBSTRING(@test,6,2) + '-' + SUBSTRING(@test,9,2) + ' ' +

SUBSTRING(@test,12,2) + ':' + SUBSTRING(@test,15,2) + ':00.000'))AS NextRun

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

ssc_san SSCommitted Points: 1573 More actions · Answer 3

SSCommitted

Points: 1573

June 27, 2011 at 1:12 pm

Thanks Gsquared and Jason.

That fixed the issue.