| Author |
Topic |
|
leonr
Starting Member
15 Posts |
 Posted - 2012-04-04 : 09:56:58
|
| Hi Guys,I basically have a table which has mixed column data types, some are nvarchar, some are date , which is ok apart from when I want to execute a stored procedure which uses LIKE , it takes the column and value to search for and simply builds a string and executes it.What I have discovered is that I can do CONVERT(nvarchar(50),value,103) and use LIKE against it, and it works for both datatypes, date and string, but I know I should not be parsing strings through the convert function.Is convert intelligent and it only converts if the input is date? and it simply ignores nvarchar and outputs the nvarchar untouched?I hope that makes sense?Thanks! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
leonr
Starting Member
15 Posts |
Posted - 2012-04-04 : 11:14:45
|
quote:
Originally posted by X002548
ummm...no, at least I don't understandIf you want to "compare" dates..you SHOULD be using date functionsMaybe if you should us what you are doing it would make more sense (a picture is worth..yada yada yada...)Look here as wellhttp://msdn.microsoft.com/en-us/library/ms186724.aspxBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
hehe yeh it's not that easy to explain.What has happened is that i've written a stored procedure which takes a column name, and a search parameters, it then returns the results.This worked perfectly until I tried to search on a date column (which is obviously not nvarchar like the other columns).Dates are stored in the format of yyyy-mm-dd , but they are displayed in the front end in english format, dd/mm/yyyy.So if i was to search a date column for %2011 it will not work.If I was to select * from table where convert(nvarchar(50),column,103) LIKE '%2011' it will work.BUT, obviously the columns *could* be either date or nvarchar, and i've now got a 'convert' stuck in my query.Basically, my query will be 'converting' the column data to date standard 103 on everything, whether its date type or nvarchar. I'll write it as code to help explain..select * from table where convert(nvarchar(50),datecolumn,103) LIKE '%2011' << this allows me to search the date value in english format, no problems here.Now i use the same query, but on a non date columnSelect * from table where convert(nvarchar(50),stringcolumn,103) LIKE '%smith' << this actually also works, but i'm not sure i should be putting nvarchar values through the convert function like this?!Maybe I need to determine the datatype first, and then have 2 queries? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-04 : 11:17:59
|
| One very easy and sensible remedy is NOT to do LIKE comparisons on date columns, which are pointless anyway. Generic search procedures are typically a poor idea, especially if they use dynamic SQL.If you could post your code it would make it easier for us to help you fix or work around this issue. |
|
|
|
leonr
Starting Member
15 Posts |
Posted - 2012-04-04 : 11:21:48
|
quote:
Originally posted by robvolk
One very easy and sensible remedy is NOT to do LIKE comparisons on date columns, which are pointless anyway. Generic search procedures are typically a poor idea, especially if they use dynamic SQL.If you could post your code it would make it easier for us to help you fix or work around this issue.
If this is the case then I will have to create some extra logic in the SP, which says if its a date column then do it this way, if not, use LIKE.No biggy, I just wanted to confirm this first :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
leonr
Starting Member
15 Posts |
Posted - 2012-04-04 : 11:25:18
|
| I would do but it won't really make too much sense if I was just to copy and paste it. My question is , what is the process/logic behind the CONVERT function? If the input type is the same as the output type, does it bother doing the conversion?I appreciate your help, im not trying to be awkward by not posting the SP, it's just it really wouldnt mean much as theres loads more too it than I have posted, i've just written about my perticulas issue/question. |
|
|
|
leonr
Starting Member
15 Posts |
Posted - 2012-04-04 : 11:31:02
|
| Thanks for your help though guys, to be honest robvolk has answered it, I should not use LIKE with dates, so therefore for good practice I should rewrite the code.Thanks :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
leonr
Starting Member
15 Posts |
Posted - 2012-04-04 : 12:12:06
|
| I Agree :D |
|
|
|
jacoboram75
Starting Member
3 Posts |
Posted - 2012-04-05 : 06:11:12
|
| So if i was to search a date column for %2011 it will not work.If I was to select * from table where convert(nvarchar(50),column,103) LIKE '%2011' it will work.BUT, obviously the columns *could* be either date or nvarchar, and i've now got a 'convert' stuck in my query.Basically, my query will be 'converting' the column data to date standard 103 on everything, whether its date type or nvarchar. ----------------------------------[url=http://www.cankayapatent.com.tr/tr/index.asp?lang=tr]Patent tescili[/url] |
|
|
|
|
CONVERT question