Kbase P3691: TO_CHAR function ingores the string format specified when co
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/12/2003 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.x
SYMPTOM(s):
Cannot specify a string format when converting Numeric data type to Char
Using SQL-92
CAUSE:
The TO_CHAR function ignores the format of a string if the field is not a Date or Time data type.
FIX:
Here after is a possible way to workaround the issue.
This concatenates 2 integer fields (year,month) and format it as follows:
[Year(4)]m[Month(2)]
When converting the field Month to character, it returns only 1 character if the value contains 1 digit indeed. The query below allows to format the field with 2 characters.
select to_char(year) + 'm' +
case
when length(ltrim(to_char(month)))=1 then '0' + ltrim(to_char(month)) else to_char(month)
end
from pub.table;