June 27, 2011 at 10:30 am
Hi All,
When I try to run the below code with @test-2 = '2009 12 17 22 31 00 000', It works fine, but If @test-2 = '' is used, It throws me an error,
How can this be fixed, Using isnull?
Please suggest.
Error:
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting datetime from character string.
declare @test-2 varchar(30)
Set @test-2 = ''
--Set @test-2 = '2009 12 17 22 31 00 000'
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
Thanks in advance
June 27, 2011 at 10:33 am
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
June 27, 2011 at 10:35 am
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.
Jason L. SelburgJune 27, 2011 at 1:12 pm
Thanks Gsquared and Jason.
That fixed the issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy