All Forums Database
Sujatha 6 posts Joined 04/08
09 Jun 2008
Truncate a decimal field without rounding

Example: 123.4567 i want to change/convert it to 123.45 and not 123.46.Please suggest some solution.

Adeel Chaudhry 773 posts Joined 04/08
09 Jun 2008

Hello,You can use double casting...and INDEX function as below:SELECT SUBSTR(CAST('123.4567' AS VARCHAR(10)), 1, INDEX('123.4567', '.')+2)HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

CarlosAL 512 posts Joined 04/08
09 Jun 2008

CAST is your friend:[font=Courier New]select cast(cast((123.4567 * 100) as integer) as numeric (9,2)) /100; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second.((123.4567*100)/100)-------------------- 123.45 BTEQ -- Enter your DBC/SQL request or BTEQ command:[/font]HTH Cheers.Carlos.

Sujatha 6 posts Joined 04/08
09 Jun 2008

Thank you Adeel. It worked fine.

Adeel Chaudhry 773 posts Joined 04/08
09 Jun 2008

Welcome! :)Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

sgarlapa 88 posts Joined 03/13
11 Feb 2014

Thanks Carlos. Your solution is very optimized and solved my problem .

You must sign in to leave a comment.