September 28, 2011 at 10:44 am
DDL & sample test data:
create table #TEST
(
idint,
totdaysvarchar(6)
);
GO
INSERT TTEST (id, totdays)
VALUES
(513,4.40),
(514,7.00),
(516,8.00),
(560,8.60),
(592,20.90),
(593,24.00),
(653,22.10),
(678,6.40),
(762,9.10),
(783,20.01),
(785,6.60),
(786,23.10),
(791,5.60),
(793,26.00),
(809,8.80),
(811,4.10),
(824,20.00),
(842,7.10),
(845,9.50);
GO
select id, totdays
from #TEST
where totdays > '20.00'
Expected results
idtotdays
59220.90
59324.00
65322.10
78320.01
78623.10
79326.00
My query pulls everything except the one that = 20.00
I've tried using cast in the where statement but still getting incorrect results.
What do I need to do to compare the whole number to get my desired results?
Thanks.
September 28, 2011 at 11:03 am
How about something like this:
with cte (id, val) as (
select id, convert(money,totdays) as val from #TEST
)
select id, val from cte where val > 20.00
The probability of survival is inversely proportional to the angle of arrival.
September 28, 2011 at 11:44 am
It's because you're doing a string comparison. For example, since the character '4' comes after the character '2', any string that begins with a '4' is greater than any string that begins with a '2'. This means that the string '4.40' is greater than the string '20.00'. If you stored your decimals as decimals instead of strings, you wouldn't have this problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 28, 2011 at 11:51 am
drew.allen (9/28/2011)
It's because you're doing a string comparison. For example, since the character '4' comes after the character '2', any string that begins with a '4' is greater than any string that begins with a '2'. This means that the string '4.40' is greater than the string '20.00'. If you stored your decimals as decimals instead of strings, you wouldn't have this problem.Drew
I understand, but I am importing historical tables from SQL 2000 & selecting only the data needed to 'cleanup' prior to loading into SQL 2008 Data Warehouse.
September 28, 2011 at 11:52 am
sturner (9/28/2011)
How about something like this:with cte (id, val) as (
select id, convert(money,totdays) as val from #TEST
)
select id, val from cte where val > 20.00
This gets me the desired results.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply