I want to convert a Date column to a formatted string in DB2. This SQL works fine:
select varchar_format(current timestamp, 'YYYY-MM')
from sysibm.sysdummy1;
but this SQL gives an error:
select varchar_format(current date, 'YYYY-MM')
from sysibm.sysdummy1;
The error is: [SQL0171] Argument 1 of function VARCHAR_FORMAT not valid.
In the first SQL, the first arg for VARCHAR_FORMAT is a timestamp, and that works. In the second SQL, the first arg for VARCHAR_FORMAT is a date, and that doesn't work.
The IBM doc implies that there's only this one function, VARCHAR_FORMAT (and its synonym, TO_CHAR).
How am I supposed to convert a DATE (not a TIMESTAMP) to a string? Or, do I have to convert the DATE to a TIMESTAMP first, then use VARCHAR_FORMAT?
I am running DB2 7.1 for i Series.
Update: converting to TIMESTAMP_ISO works. But it's ugly:
select varchar_format(timestamp_iso(current date), 'YYYY-MM')
from sysibm.sysdummy1;
That one works.