Example of custom DB2 date/time format

Custom DB2 date is a common problem. What if your customer wants the date format to be YYYYMMDD? The best method is to write a custom Formatting Function:

The following is an example of customizing the DB2 date function:

 
 

  
  
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 'yyyy/dd/mm hh:mi:ss'   
  
  
then yyyy || '/' || mm || '/' || dd || ' ' ||   
  
  
hh || ':' || mi || ':' || ss   
  
  
when 'nnnnnn'   
  
  
then nnnnnn   
  
  
else   
  
  
'date format ' || coalesce(fmt,' ') ||   
  
  
' not recognized.'   
  
  
end   
  
  
from tmp  
 
 

This formula is complex at first glance. After a closer look, you will find it easy to use. First, use the Common Table Expression to extract each part of the time format, and then reassemble the output according to the date format provided by the user. This function is flexible. You can simply add the WHEN clause to add the expected date format. When using a function, if the input date format does not exist, the function can also output error information.

Tips for DB2 data deletion

DB2 event monitoring command

How to Implement online DB2 Import

DB2 Online Export Method

Common db2 stored procedure statements