Left two digits out of seven digits

[dwight1]

I have a field (DATA type Float)with seven digits in it. I want to filter out the first three digits. I am using the following expression.

LEFT (dbo.A_CombinedDeposits.ACCTNO, 3) it gives me the following


6007862 ------ 6.0
7006823 ------- 7.0

I do not know why it is adding decimal point to it and counting it as a digit.

Thanks

Dwight

 

[SQLDenis]

Do this instead

declare @v float
select @v =6007862

select left(@v,3),left(convert(decimal(20,8),@v),3)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Because 6007862 gets implicitely converted to scientific notation (6.00786e+006).

Try explicit conversion to int before:

declare @blah float
set @blah = 6007862  

select left(@blah, 3)
select left(convert(int, @blah), 3)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dwight1]

Thank you SQLDenis and Vongrunt.

Vongrunt,

Your code worked perfectly. For learning purposes can you tell me, Why do we require the third line of your code.

Select Left(@blah, 3)

Thank you

Dwight

 

[dwight1]

Cont....

I am sorry. It worked but for only

set @blah = 6007862

It definitely took the three left digits from the above number to give me result of 600. But I have a field called blah with almost 10,000 numbers how do i apply on them.

Thanks again

Dwight

 

[druer]

Dwight,

All you need to do is change your original query from:
LEFT (dbo.A_CombinedDeposits.ACCTNO, 3)

to:

LEFT (convert(int,dbo.A_CombinedDeposits.ACCTNO), 3)

All you are trying to do is convert the Float to an INT before trying to use the LEFT function.