sib_user
asked on
Apparant floating point error inconsistency
The following query runs without error:
select batchno, pid , 2010 - convert(float,right(Q8_3_2
from
[Census2010-CLEAN].[dbo].[
where
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null
yet, this one gives the error Error converting data type nvarchar to float :
select batchno, pid
from
[Census2010-CLEAN].[dbo].[
where
2010 - convert(float,right(Q8_3_2
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null
Don't they do the same conversion on the same data? The only difference from my point of view is that in the second query the conversion happens in the where clause.
Will somebody please explain this apparent inconsistency.
select batchno, pid , 2010 - convert(float,right(Q8_3_2
from
[Census2010-CLEAN].[dbo].[
where
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null
yet, this one gives the error Error converting data type nvarchar to float :
select batchno, pid
from
[Census2010-CLEAN].[dbo].[
where
2010 - convert(float,right(Q8_3_2
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null
Don't they do the same conversion on the same data? The only difference from my point of view is that in the second query the conversion happens in the where clause.
Will somebody please explain this apparent inconsistency.
devlab2012
check the values in column Q8_3_2DOB. There must be some non-numeric values.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
devlab2012,
Thanks for your suggestions. I agree with you that there must be some non numeric characters in the year portion of the dob field and have tried filtering the out by including the following condition in the whereclause:
and right(Q8_3_2DOB,4) not like '%[a-zA-Z]%'
yet that made no difference. What I need is a condition that says 'where the last characters of the dob field are only numeric characters'
Sarath_123:
I have tried your suggestion and get the same error unfortunately.
Thanks for your suggestions. I agree with you that there must be some non numeric characters in the year portion of the dob field and have tried filtering the out by including the following condition in the whereclause:
and right(Q8_3_2DOB,4) not like '%[a-zA-Z]%'
yet that made no difference. What I need is a condition that says 'where the last characters of the dob field are only numeric characters'
Sarath_123:
I have tried your suggestion and get the same error unfortunately.
ASKER
Experts:
This works:
select batchno, pid
from
[Census2010-CLEAN].[dbo].[
where
2010 - convert(float,right(Q8_3_2
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null
and isnumeric(right(Q8_3_2DOB,
This works:
select batchno, pid
from
[Census2010-CLEAN].[dbo].[
where
2010 - convert(float,right(Q8_3_2
charindex(' ', right(Q8_3_2DOB,4)) = 0 and Q8_3_2DOB is not null
and isnumeric(right(Q8_3_2DOB,
ASKER
I made a mistake awarding the points here: my intention was to split the points between devlab's two answers. Is there any way to undo my error?
Request the moderator.