Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
oasis1
Starting Member
35 Posts |
 Posted - 2015-01-13 : 10:22:04
|
| Aloha, I am looking at some sql someone has written using a "between" operator. The data isn't the cleanest and the are using it as between '254.99' and '256.78'. I don't have access to the system to check but was wondering would sql server do an implicit conversion? is there a better way to set this up? Should it be converted to decimal? As I mentioned not sure how clean the data is so may be some text in there and it could bomb out if trying to convert. mahalo,The Dude |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-01-13 : 11:39:41
|
| Please post the SQL in question and the declarations of the columns or variables involved.BETWEEN is just short-form. e.g.a BETWEEN b AND c is equivalent to (and compiles the same as)a >= b and a <= cComparing as strings this way though might not give you the results you want. e.g.select case when '0254.99' BETWEEN '254.99' and '256.78' THEN 'BETWEEN' ELSE 'NOT BETWEEN' ENDreturns 'NOT BETWEEN' |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-01-13 : 13:08:51
|
quote:
would sql server do an implicit conversion?
No, not unless you compared those values to a numeric value. SQL doesn't convert data unless it has to. |
|
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2015-01-14 : 10:22:07
|
| You may want to add a step by creating a temporary table in which you convert to decimal. You can also decide what to replace the "bad" data with (NULL, 0.0).djj |
|
|
|
|
|
|
Using between on varchar fields