MySQL

DB2 equivalent

Description

LOCALTIME, 

CURRENT_TIME, 

Are all synonyms for 

LOCALTIME(), 

CURRENT_DATE, 

NOW() which returns date 

LOCALTIMESTAMP, 

CURRENT_TIMESTAMP

and time in YYYY MM DD 

LOCALTIMESTAMP()

HH:MM:SS or 

YYYYMMDDHHMMSS 

format.

select octet_length(`abc')

LENGTH

OCTET_LENGTH is in 

MySQL a synonym for 

LENGTH

select positional' in `hello')

VALUES POSSTR(`hello' , 

Returns the position of the 

`ll') 

search string

select substring(`abcd' 

SUBSTR(`abcd', 2, 2)

 ANSI SQL SUBSTRING

from 2 for 2)

select trim(trailing from 

Use RTRIM and LTRIM

Combination of LTRIM and 

trim(LEADING FROM ` abc 

RTRIM

`))

Functions according to ODBC declaration

ODBC 3.0 requires a wide range of built in function and it is amazing to find only 

minor differences between the two DBMS. Please note that not all functions have 

been tested for their results. Specifically when performing date and time 

arithmetic using the WEEK function and the DATE function listed in Table 7 4, the 

results between MySQL and DB2 maybe different. However, these may not be 

the only ones returning different results.

Table 7 4   ODBC 3.0 functions

MySQL

DB2

Description

select 

VALUES 

DAY returns the day 

dayofmonth(`2004 02 23') 

DAY(`2004 02 23')

portion of the argument

from t1

select hour(TIME 

VALUES 

Slightly different syntax

`12:13:14')

HOUR(TIME(`12:13:14'))

WEEK

WEEK

DB2 starts at week 1, 

MySQL starts at week 0. 

DB2 also provides 

WEEK_ISO

 Chapter 7. Application porting 

169