truncate field 1

[GShen]

Is there a way of truncating a field as below:
.3353, I want to be .33. I do not want to round it. I tried ROUND and of course I get .34. I tried casting to a numeric field of (5.2) and it rounded it also to .34. It is a $ field and I am trying to compare. I do not want .3353 to become .34 and have the other field I am comparing to, say .3349 become .33 and have a mismatch. I am still searching and trying stuff in the meantime.
Thanks,

Remember when... everything worked and there was a reason for it?

 

[gmmastros]

You could multiply by 100, use the floor function (which always rounds down) and then divide by 100.

Ex:

Declare @Temp Float

Set @Temp = 0.3353

Select @Temp * 100
Select Floor(@Temp * 100)
Select Floor(@Temp * 100) / 100

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[GShen]

Thanks, That is what I just did. Actually I mult. by 100 and casted to an int field. Just did my compare with whole numbers. This is for a WHERE condition and it seems to be working fine.

Remember when... everything worked and there was a reason for it?

 

[gmmastros]

Alternatively... you could subtract 0.005 and then round to the second digit, like this:

Declare @Temp Table(Value Money)

Insert Into @Temp Values(0.3300)
Insert Into @Temp Values(0.3340)
Insert Into @Temp Values(0.3349)
Insert Into @Temp Values(0.3350)
Insert Into @Temp Values(0.3359)
Insert Into @Temp Values(0.3390)
Insert Into @Temp Values(0.3399)
Insert Into @Temp Values(0.3299)
Insert Into @Temp Values(-0.3299)
Insert Into @Temp Values(-0.3300)
Insert Into @Temp Values(-0.3350)
Insert Into @Temp Values(-0.3399)

Select Value, Round(Value - 0.005, 2), Floor(Value * 100) / 100
From   @Temp

Notice that both methods give slightly different results when the values are negative. You should understand the difference and choose the method that best suits your requirements.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom