July 22, 2013 at 11:45 pm
I have a query in one of my procedures which causes an error.
select substring(expirydate,1,(charindex(' ',expirydate) -1)) as expirydate
from newtable
this query looks through my expiry date field and looks for the space minus 1 character and extracts the 30/04/2012 from my 30/04/2012 08:14:23 date and time. all is good at this point but I run into errors when the dates in the columns are like this
30/04/2012
or
5/011/2012
as there are no spaces so it cannot find the charindex value which I think causes an error
Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
does this make sense and is there a way round it
July 22, 2013 at 11:57 pm
Oracle765 (7/22/2013)
I have a query in one of my procedures which causes an error.
select substring(expirydate,1,(charindex(' ',expirydate) -1)) as expirydate
from newtable
this query looks through my expiry date field and looks for the space minus 1 character and extracts the 30/04/2012 from my 30/04/2012 08:14:23 date and time. all is good at this point but I run into errors when the dates in the columns are like this
30/04/2012
or
5/011/2012
as there are no spaces so it cannot find the charindex value which I think causes an error
Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
does this make sense and is there a way round it
A couple of problems that I see here:
1. It appears you're storing dates as a VARCHAR when you should be trying to use a SQL DATETIME type.
2. Your front-end application isn't scrubbing the date format properly (5/011/2012).
But if your answer to the above is that you have no control over these things (which you should most definitely try to correct), then you can CASE around the issue something like this:
select CASE charindex(' ',expirydate) WHEN 0 THEN expirydate
ELSE substring(expirydate,1,(charindex(' ',expirydate) -1)) END as expirydate
from newtable
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply