Truncate a decimal field without rounding

Database

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.
5 REPLIES
Teradata Employee

Re: Truncate a decimal field without rounding

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

Re: Truncate a decimal field without rounding

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.

Re: Truncate a decimal field without rounding

Thank you Adeel. It worked fine.
Teradata Employee

Re: Truncate a decimal field without rounding

Welcome! :)

Regards,

Adeel

Re: Truncate a decimal field without rounding

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