Convert SQL Function from SQL Server to MySQL

The list below illustrates how to convert the most popular SQL Server built-in functions and operators into MySQL equivalents:

• SQL Server offers operator '+' to concatenate strings. MySQL offers function CONCAT for the same purpose:

  SELECT CONCAT(CONCAT('Hello', ','), ' world');
  

• SQL Server uses function CHARINDEX to search substring into string. MySQL equivalent for this function is LOCATE.
• SQL Server function CONTAINS(expression, template) searches for matches of template inside expression. MySQL has operator LIKE that implements the same semantics: expression LIKE %template%
• CONVERT() function is used to convert an expression of one data type to another in SQL Server. In MySQL CONVERT() function converts text data between different character sets. However, there is equivalent function CAST(), so every occurrence of convert(type, expression) in MS SQL query must be replaced by cast(expression AS type) in MySQL query. See special note on using CONVERT() for DATETIME values.
• SQL Server function IIF($boolean_expression, $true_value, $false_value) that returns $true_value or $false_value value depending on $boolean_expression, must be replaced by IF in MySQL.
• LEN() function returns length of string expression in MS SQL. MySQL equivalent for this function is LENGTH().
• SQL Server function DATEADD adds interval to the specified part of the date. MySQL operator '+' can do the same as follows:

  DATEADD(year,  1, $date$) -> $date$ + interval 1 year
  DATEADD(month, 1, $date$) -> $date$ + interval 1 month
  DATEADD(day,   1, $date$) -> $date$ + interval 1 day
  

  where $date$ is an expression of DATE type. Function DATEDIFF($datepart, $startdate, $enddate) returns $datepart part of substructing $startdate from $enddate. It can be converted into MySQL as follows:

  DATEDIFF(hour, $startdate, $enddate)   -> TIMESTAMPDIFF(hour, $enddate, $startdate)
  DATEDIFF(minute, $startdate, $enddate) -> TIMESTAMPDIFF(minute, $enddate, $startdate)
  DATEDIFF(month, $startdate, $enddate)  -> TIMESTAMPDIFF(month, $enddate, $startdate)
  

In general, SQL Server and MySQL have different sets of date processing functions, although most of them can be replicated as follows:

DATENAME(month,   $date$) -> DATE_FORMAT($date$, '%M') or MONTHNAME(expression)
DATENAME(weekday, $date$) -> DATE_FORMAT($date$, '%W') or DAYNAME(expression)
DATEPART(year,    $date$) -> DATE_FORMAT($date$, '%Y')
DATEPART(month,   $date$) -> DATE_FORMAT($date$, '%m')
DATEPART(day,     $date$) -> DATE_FORMAT($date$, '0')
GETDATE()                 -> NOW()
GETUTCDATE()              -> UTC_TIMESTAMP()

where $date$ is an expression of DATE type.

• SCOPE_IDENTITY() returns last inserted value into an identity column in SQL Server. It must be replaced by MySQL equivalent LAST_INSERT_ID().

Have questions? Contact us