January 18, 2012 at 12:22 am
@ VIG: Received error message
'Invalid length parameter passed to the LEFT or SUBSTRING function.'
January 18, 2012 at 12:40 am
mic.con87 (1/18/2012)
@ VIG: Received error message'Invalid length parameter passed to the LEFT or SUBSTRING function.'
Probably there is data less than 7 characters
Give me an example of such data, pls
January 18, 2012 at 12:45 am
There are 0's in the data set when no data was found. The two distinct character lengths are 8 and 1
January 18, 2012 at 1:35 am
try this
with
q as (select CurrentDateFormat
,case when CurrentDateFormat=0
then '19000101'
else right(CAST(CurrentDateFormat as varchar(8)),4)
+ left(right(CAST(CurrentDateFormat as varchar(8)),6),2)
+ left(CurrentDateFormat,len(CAST (CurrentDateFormat as varchar(8)))-6)
end dt
from #Dates d
)
select CurrentDateFormat,cast(case when ISDATE(dt)=1 then dt else '19000101' end as datetime)
from q
January 18, 2012 at 1:43 am
Works great! Thanks for the alternative method:-)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply