CAST Function - SQL Server to MariaDB Migration

In SQL Server you can use CAST function to convert an expression to another data type. In MariaDB 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);

MariaDB:

  -- 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 unlike MySQL's CAST, MariaDB supports CAST AS INTEGER.

SQL Server:

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

MariaDB:

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

For more information, see SQL Server to MariaDB Migration.