CODEWITHSUNDEEP
sqldb2decimalimplicit-conversionseparator
user15221309
user15221309

Reputation: 1

Conversion of decimal separator - DB2 SQL

I want to convert a double value column in DB2 SQL with ',' comma decimal separator to '.' dot decimal separator. My local settings are German. I can't change the DB settings since many applications are involved.

Eg : column value =0,81234 I want it to be displayed as 0.81234.

SELECT CAST(CAST(COLUMNNAME AS DECIMAL(8,2)) AS VARCHAR(25))

I tried converting it to decimal(8,2) first and then to varchar. This results in 0.81 (with '.' as expected). But I need all the numbers right of the decimal separator like 0.81234. So, I tried with decimal(7,6) which results in SQL0413N OVERFLOW OCCURRED DURING NUMBER DATA TYPE CONVERSION error.

Is there any other way to display the decimal separator as '.'?

Upvotes: 0

Views: 2020

Answers (2)

Starryk
Starryk

Reputation: 68

if you use JDBC you can modify it by adjusting your Connection string. Just add this

:decimalSeparator=1;

for point as decimal separator or 2 for comma as decimal separator

Upvotes: 0

Charles
Charles

Reputation: 23803

The separator is not actually stored in the DB for numeric columns.

Configure whatever tool you are using to look at the data to use your separator of choice.

Optionally you can run
set option decmpt = *PERIOD;

before running your select.

Upvotes: 1

Related Questions