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.
- 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:
1 ----------- 20/08/2003
- 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.
- 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 xxThe output will be as follows:
1 ------------------- 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.