Get started Bring yourself up to speed with our introductory content.

Formatting the date with SQL

Here's a simple way to provide formatting for dates in DB2.

Here is a simple way to provide a format for date fields in DB2. More complicated formating will need to use the to_char() function available on IBM's web site.

  1. To get the date in the format dd/mm/yyyy from a date field, use
    select replace(char(date_field,eur),'.','/'),10) from xx;
    The output will be:
  2. If your field is a timestamp you can use the following formatting technique:
     select char(replace(char(date(timestamp_field),eur),'.','/') ,10) from xx;
    The output will be same as in #1 above.
  3. If you also need to add the timestamp to the output you can use the following:
    select concat(char(replace(char(date(timestamp_field),eur),'.','/') ,11),char(time(timestamp_field))) from xx
    The output will be as follows:
    20/08/2003 11:32:57

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free DB2 tips and scripts.
  • Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: DB2 tips, tutorials, and scripts from around the Web.

Dig Deeper on iSeries SQL commands and statements