In MySQL, DATE_FORMAT function converts a DATE or DATETIME value to string using the specified format. In DB2, you can use VARCHAR_FORMAT function.
Note that the DATE_FORMAT and VARCHAR_FORMAT use different format strings.
MySQL:
-- Convert the current date to YYYYMM format SELECT DATE_FORMAT(NOW(), '%Y%m'); # 201302
DB2:
-- Convert the current date to YYYYMM format SELECT VARCHAR_FORMAT(CURRENT_DATE, 'YYYYMM') FROM sysibm.sysdummy1; # 201302
When you convert MySQL DATE_FORMAT function to DB2 VARCHAR_FORMAT function you have to map the format specifiers:
MySQL DATE_FORMAT | DB2 VARCHAR_FORMAT | ||
1 | %Y | 4-digit year | YYYY |
2 | %y | 2-digit year, 20th century for 00-49 | RR |
3 | %b | Abbreviated month (Jan - Dec) | MON |
4 | %M | Month name (January - December) | MONTH |
5 | %m | Month (0 - 12) | MM |
6 | %a | Abbreviated day (Sun - Sat) | DY |
7 | %d | Day (0 - 31) | DD |
8 | %H | Hour (0 - 23) | HH24 |
9 | %h | Hour (1 - 12) | HH or HH12 |
10 | %i | Minutes (0 - 59) | MI |
11 | %s | Seconds (0 - 59) | SS |
Typical conversion examples:
MySQL | DB2 | Sample Output | |
1 | DATE_FORMAT(NOW(), '%Y-%m-%d) | VARCHAR_FORMAT(CURRENT_DATE, 'YYYY-MM-DD') | 2013-02-14 |
2 | DATE_FORMAT(NOW(), '%d/%m/%y') | VARCHAR_FORMAT(CURRENT_DATE, 'DD/MM/RR') | 14/02/13 |
SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.
Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.