TO_CHAR(datetime, format) has wrong return type for non-constant format parameter
Details
| Detail name | Value |
|---|---|
| Changelog Number | 17755 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0 |
| Fix Versions | Exasol 8.22.0 |
| Resolution Date | 2023-08-29 |
Description
If the following conditions are fulfilled:
- The TO_CHAR function is called with two or three parameters.
- The first parameter is a datetime type.
- The second parameter, i.e., the format, is not a constant.
Then TO_CHAR returns the wrong string type (wrong length or ASCII instead of UTF8). The returned string itself is correct, unless the wrong string type is too short for the result, in which case the function call fails and yields a data exception.
Example
CREATE TABLE timestamps(ts TIMESTAMP, ts_format VARCHAR(100));
INSERT INTO timestamps VALUES ('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
-- Bug: The string type of this column uses ASCII instead of UTF8:
SELECT TO_CHAR(ts, ts_format) FROM timestamps;
Workaround
Use a constant format string as the second parameter for TO_CHAR. For example:
SELECT TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS') FROM timestamps;
Fix
The function returns the correct string type in these cases.
