How to use dd/mm/yyyy date format on DB2
On db2 doesn't exist a function to format date when you want to change the order among day,month and year or change the symbols thah delimit day,montyh and year. If you to use it you have to create the funcion below to execute these tasks.
Create function ts_fmt(TS timestamp, fmt varchar(20))
returns varchar(50)
return
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as
(
select
substr( digits (day(TS)),9),
substr( digits (month(TS)),9) ,
rtrim(char(year(TS))) ,
substr( digits (hour(TS)),9),
substr( digits (minute(TS)),9),
substr( digits (second(TS)),9),
rtrim(char(microsecond(TS)))
from sysibm.sysdummy1
)
select
case fmt
when 'yyyymmdd'
then yyyy || mm || dd
when 'mm/dd/yyyy'
then mm || '/' || dd || '/' || yyyy
when 'dd/mm/yyyy'
then dd || '/' || mm || '/' || yyyy
when 'yyyy/dd/mm hh:mi:ss'
then yyyy || '/' || mm || '/' || dd || ' ' ||
hh || ':' || mi || ':' || ss
when 'nnnnnn'
then nnnnnn
else
'date format ' || coalesce(fmt,' <null> ') ||
' not recognized.'
end
from tmp
end@
Usage examples:
values ts_fmt(current timestamp,'dd/mm/yyyy')
'01/05/2010'
values ts_fmt(current timestamp,'nothing')
'date format nothing not recognized.'