DB2中时间型字段的转换

数据库中有三种时间类型字段，分别是date,time,timestamp
日期转字符串比较麻烦

VARCHAR_FORMAT等同于to_char

日期型转换成字符串型时，第一个参数不能是date型，必须是timestamp型。

可以使用timestamp_iso函数先进行转换一次

 

	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.