CAST Function - SQL Server to MySQL Migration

In SQL Server you can use CAST function to convert an expression to another data type. In MySQL you can also use CAST function but data types can be different.

SQL Server:

  -- Convert number to string
  SELECT CAST(123.45 AS VARCHAR); 
 
  -- Convert string to datetime
  SELECT CAST('2024-10-21 14:54:27.123' AS DATETIME);

MySQL:

  -- Convert number to string - CHAR is required (!)
  SELECT CAST(123.45 AS CHAR); 
 
  -- Convert string to datetime - DATETIME(3) is required otherwise milliseconds are truncated
  SELECT CAST('2024-10-21 14:54:27.123' AS DATETIME(3));

Note that MySQL does not support CAST AS INTEGER and SIGNED INTEGER or UNSIGNED INTEGER should be used.

SQL Server:

  -- Convert string to number
  SELECT CAST('123' AS INT); 
  # 123
 
  SELECT CAST('123' AS INTEGER); 
  # 123

MySQL:

  -- Convert string to number
  SELECT CAST('123' AS INT); 
  # ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 
  # to your MySQL server version for the right syntax to use near 'INT)'
 
  SELECT CAST('123' AS INTEGER); 
  # ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds 
  # to your MySQL server version for the right syntax to use near 'INTEGER)'  
 
  -- Convert string to number
  SELECT CAST('123' AS UNSIGNED INT); 
  # 123
 
  SELECT CAST('123' AS UNSIGNED INTEGER); 
  # 123

For more information, see SQL Server to MySQL Migration.