| Author |
Topic |
|
shaik.zakeer
Posting Yak Master
117 Posts |
 Posted - 2009-07-16 : 06:14:07
|
| Hi what is the best way to avoid like operator during search conditions?Thanks in advanceThanks Zakeer Sk |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-16 : 06:40:13
|
| It is recommended that LIKE be used when you search for datetime values Only...-------------------------R.. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-16 : 06:42:23
|
quote:
Originally posted by rajdaksha
It is recommended that LIKE be used when you search for datetime values Only...
What?Can you please elaborate on that?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-16 : 06:45:12
|
| Datetime entries can contain a variety of dateparts.For example, if we are insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.-------------------------R.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-07-16 : 08:42:04
|
quote:
Originally posted by rajdaksha
Datetime entries can contain a variety of dateparts.For example, if we are insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.-------------------------R..
You should always use proper DATETIME datatype to store dates. That makes the thing easy for all calculationsMadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-16 : 10:58:31
|
quote:
Originally posted by rajdaksha
A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.
How would you know the time returned isn't 9:20PM instead of 9:20AM?I agree with Madhivanan. Always use proper datatype to store your data. In this case you seem to already use a DATETIME, or SMALLDATETIME but you are converting the datetime value to a string and search that value.SELECT *FROM ...WHERE DATEADD(DAY, DATEDIFF(DAY, Col1, 0), Col1) = '09:00'SELECT *FROM ...WHERE DATEADD(DAY, DATEDIFF(DAY, Col1, 0), Col1) = '21:00'
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-07-16 : 11:09:34
|
| Interesting diversion certainly......However,shaik.zakeer: What do you mean?Can you give an example of which situation you are using LIKE in and what you hope to achieve.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-07-16 : 12:37:47
|
quote:
Originally posted by rajdaksha
It is recommended that LIKE be used when you search for datetime values Only...
I think I just bruised my jaw on the floor.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-07-16 : 12:39:25
|
quote:
Originally posted by rajdaksha
Datetime entries can contain a variety of dateparts.For example, if we are insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.
Ouch. I did it again.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-07-19 : 19:48:24
|
quote:
Originally posted by rajdaksha
It is recommended that LIKE be used when you search for datetime values Only...-------------------------R..
Absolutely, positively, unequivocally, irrevocably, and most certainly not. As Madhivanan and Peso have pointed out, you should always use the proper datetime datatype for any and all date manipulation and criteria.Blindman... do you know a good dentist? I believe I've fractured my jaw on this one.--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row" |
|
|
|
|
how can we avoid like operator?