https://www.sqlines.com/ 2026-04-14T05:20:32+00:00 https://www.sqlines.com/ https://www.sqlines.com/lib/images/favicon.ico text/html 2022-12-10T17:03:46+00:00 https://www.sqlines.com/sql-server-to-mariadb/blank_after_func_name?rev=1670691826&do=diff In SQL Server you can have blank characters after the function name before parentheses. This is not allowed for some (not every) functions in MariaDB and blanks must be removed. SQL Server: -- A blank after the function name SELECT SUBSTRING ('abc', 1, 2); # ab -- A newline after the function name SELECT SUBSTRING ('abc', 1, 2); # ab text/html 2024-10-23T18:33:17+00:00 https://www.sqlines.com/sql-server-to-mariadb/cast?rev=1729708397&do=diff 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); text/html 2022-07-19T19:06:49+00:00 https://www.sqlines.com/sql-server-to-mariadb/cast_datetime?rev=1658257609&do=diff In SQL Server, you can use CAST function to convert a string in various formats to a DATETIME value. In MariaDB, you have to use STR_TO_DATE function and explicitly specify the format. SQL Server: SELECT CAST('19-Jul-2022' AS DATETIME); # 2022-07-19 00:00:00.000 SELECT CAST('07/19/2022' AS DATETIME); # 2022-07-19 00:00:00.000 SELECT CAST('2022-07-19' AS DATETIME); # 2022-07-19 00:00:00.000 text/html 2022-07-19T19:29:44+00:00 https://www.sqlines.com/sql-server-to-mariadb/cast_datetime_hex?rev=1658258984&do=diff In SQL Server, you can use CAST function with a hexadecimal value to convert it to a DATETIME value. In MariaDB, you have to use an interval expression (see below): SQL Server: SELECT CAST(0x0000A074004308F0 AS DATETIME); # 2012-06-18 04:04:04.000 text/html 2026-01-21T15:41:53+00:00 https://www.sqlines.com/sql-server-to-mariadb/ceiling?rev=1769010113&do=diff In SQL Server and MariaDB, the CEILING function rounds up the specified number to the nearest integer value. SQL Server and MariaDB: -- Round to the nearest larger integer SELECT CEILING(123.7); /* 124 */ -- Still rounds to larger integer even if it is closer to smaller integer SELECT CEILING(123.1); /* 124 */ text/html 2024-11-06T13:17:21+00:00 https://www.sqlines.com/sql-server-to-mariadb/char_function?rev=1730899041&do=diff In SQL Server, the CHAR function converts an integer ASCII code (0-255) to the corresponding character value. MariaDB also provides the CHAR function but it returns a binary string by default, so you should add USING ASCII clause. SQL Server: -- CHAR(32) returns a blank character SELECT CHAR(32); /* Result: ' ' */ -- NULL is returned for values exceeding 255 SELECT CHAR(270); /* NULL */ text/html 2023-01-19T14:55:18+00:00 https://www.sqlines.com/sql-server-to-mariadb/convert_datetime?rev=1674140118&do=diff In SQL Server, you can use CONVERT function to convert a string with the specified format to a DATETIME value. In MariaDB, you can use STR_TO_DATE function if you need a specific format, or CONVERT if you need the default format. Note that the order of parameters in SQL Server and MariaDB CONVERT functions is different. text/html 2022-12-09T21:18:11+00:00 https://www.sqlines.com/sql-server-to-mariadb/convert_numeric?rev=1670620691&do=diff In SQL Server you can use CONVERT function to convert an expression to a number and also use it to truncate or round numeric value. In MariaDB you can also use CONVERT function but note that the order of parameters is different. Also MariaDB CONVERT does not support NUMERIC as type, you have to use DECIMAL. text/html 2024-07-28T10:13:46+00:00 https://www.sqlines.com/sql-server-to-mariadb/convert_string_datetime?rev=1722161626&do=diff In SQL Server you can use CONVERT function to convert a DATETIME value to a string with the specified style (string format). In MariaDB you can use the DATE_FORMAT function. SQL Server: -- 3rd parameter specifies 112 style (Date 'YYYYMMDD' format) SELECT CONVERT(CHAR(8), GETDATE(), 112); # 20170406 text/html 2024-10-07T09:51:04+00:00 https://www.sqlines.com/sql-server-to-mariadb/cross_apply?rev=1728294664&do=diff SQL Server CROSS APPLY clause allows you to perform a correlated inner join (lateral join) often using a table-valued function or subquery. Correlated means that the right operand of a join clause contains a reference to the left operand. In MariaDB you can use the CROSS JOIN LATERAL clause (still in progress, see <https://jira.mariadb.org/browse/MDEV-19078>). text/html 2023-02-19T14:11:14+00:00 https://www.sqlines.com/sql-server-to-mariadb/dateadd?rev=1676815874&do=diff In SQL Server, you can use DATEADD function to add an interval in the specified units to the datetime value. In MariaDB, you can use TIMESTAMPADD function. Note that unit values can be different in SQL Server DATEADD and MariaDB TIMESTAMPADD functions. text/html 2022-09-05T20:31:01+00:00 https://www.sqlines.com/sql-server-to-mariadb/datediff?rev=1662409861&do=diff In SQL Server, you can use DATEDIFF function to get the datetime difference in specified units. In MariaDB, you can use TIMESTAMPDIFF function. Note that unit values can be different in SQL Server DATEDIFF and MariaDB TIMESTAMPDIFF. SQL Server: text/html 2023-02-15T15:40:45+00:00 https://www.sqlines.com/sql-server-to-mariadb/format?rev=1676475645&do=diff In SQL Server FORMAT function converts a datetime value to string using the specified format. In MariaDB you can use DATE_FORMAT function. Note that the FORMAT and DATE_FORMAT format strings are different. SQL Server: -- Convert the current date and time to string (year-month-day) SELECT FORMAT(GETDATE(), 'yyyyMMdd'); # 20230215 text/html 2024-07-28T10:14:37+00:00 https://www.sqlines.com/sql-server-to-mariadb/getdate?rev=1722161677&do=diff In SQL Server the GETDATE function returns the current date and time with milliseconds precision without the time zone for the current database instance. In MariaDB you can use the NOW(3) function. SQL Server: -- Get the current date and time with milliseconds precision SELECT GETDATE(); # 2024-07-28 13:13:11.640 text/html 2025-01-08T15:11:22+00:00 https://www.sqlines.com/sql-server-to-mariadb/identity?rev=1736349082&do=diff In SQL Server, the IDENTITY column property allows you to automatically assign incremental integer values to a column. In MariaDB, you can use the AUTO_INCREMENT property: SQL Server: -- Table with identity values starting at 1 and incrementing by 1 by default CREATE TABLE colors ( id INT IDENTITY, name VARCHAR(10) ); -- Insert a few sample rows INSERT INTO colors (name) VALUES ('Blue'); INSERT INTO colors (name) VALUES ('White'); INSERT INTO colors (name) VALUE… text/html 2024-10-26T15:41:11+00:00 https://www.sqlines.com/sql-server-to-mariadb/inserted?rev=1729957271&do=diff In SQL Server, inserted is a system table available in a statement-level trigger that holds all inserted or updated rows. In MariaDB you have to use a row-level trigger and the direct column reference using the NEW column prefix. Consider a sample table with a trigger: text/html 2024-10-29T08:49:22+00:00 https://www.sqlines.com/sql-server-to-mariadb/isnull?rev=1730191762&do=diff In SQL Server, you can use the ISNULL function to replace NULL values. In MariaDB, you can use the IFNULL function. SQL Server: -- Replace NULL with N/A string SELECT ISNULL(NULL, 'N/A'); # N/A MariaDB: -- Replace NULL with N/A string SELECT IFNULL(NULL, 'N/A'); # N/A text/html 2024-10-04T09:31:38+00:00 https://www.sqlines.com/sql-server-to-mariadb/json_query?rev=1728034298&do=diff In SQL Server, you can query JSON data and extract an object or array (but not a scalar value) using JSON_QUERY function. In MariaDB you can use JSON_EXTRACT function. SQL Server: -- Extract "color" object SELECT JSON_QUERY('{"color":{"name":"Red","category":"R"}}', '$.color'); # {"name":"Red","category":"R"} -- Returns NULL as JSON_QUERY cannot be used to extract scalar values SELECT JSON_QUERY('{"name":"Red","category":"R"}', '$.name'); # NULL -- If PATH is not specifie… text/html 2024-10-24T05:44:56+00:00 https://www.sqlines.com/sql-server-to-mariadb/len?rev=1729748696&do=diff In SQL Server, the LEN function returns the length of the string in characters excluding trailing spaces. In MariaDB, you can use the CHAR_LENGTH function that includes the traling blanks, so you can also apply the RTRIM function if you want to exclude them. text/html 2025-01-08T12:52:50+00:00 https://www.sqlines.com/sql-server-to-mariadb/money?rev=1736340770&do=diff In SQL Server, the MONEY data type stores 64-bit monetary values, decimal numbers with up to 4 digits after the decimal point. In MariaDB, you can use the DECIMAL(19,4) data type. SQL Server: -- Sample table CREATE TABLE products ( name VARCHAR(30), price MONEY ); -- Sample data INSERT INTO products VALUES ('Apple', 19.95); text/html 2024-10-24T05:57:19+00:00 https://www.sqlines.com/sql-server-to-mariadb/month?rev=1729749439&do=diff In SQL Server and MariaDB, the MONTH function extracts the month number from the datetime. SQL Server: -- Get the month SELECT MONTH('2024-10-24'); # 10 MariaDB: -- Get the month SELECT MONTH('2024-10-24'); # 10 For more information, see SQL Server to MariaDB Migration. text/html 2024-10-26T17:58:06+00:00 https://www.sqlines.com/sql-server-to-mariadb/newid?rev=1729965486&do=diff In SQL Server, the NEWID function generates an Universal Unique Identifier (UUID) of UNIQUEIDENTIFIER data type. In MariaDB, you can use the UUID function that returns a UUID as a string. SQL Server: -- Generate UUID SELECT NEWID(); # 63579ACA-AE07-4290-9BAF-A72261B5FC44 text/html 2026-01-20T21:01:58+00:00 https://www.sqlines.com/sql-server-to-mariadb/nvarchar?rev=1768942918&do=diff In SQL Server, the NVARCHAR(n) data type stores variable-length Unicode UCS-2 or UTF-16 character strings up to n byte-pairs with the maximum of 4,000 byte-pairs or 2 GB if NVARCHAR(max) is specified. In MariaDB, you can use NVARCHAR(n), which can store up to 65,535 characters. However, the effective maximum length is subject to the maximum row size limit of 65,535 bytes (shared among all columns) and the character set used. text/html 2024-10-05T15:06:48+00:00 https://www.sqlines.com/sql-server-to-mariadb/openjson?rev=1728140808&do=diff In SQL Server, you can use OPENJSON table-valued function to generate rows and columns from JSON data. In MariaDB you can use JSON_TABLE function, but its syntax and parameters are different. SQL Server: -- Convert JSON array to rows SELECT * FROM OPENJSON('[{"a":1, "b":"b1", "c":true}, {"a":2, "b":"b2", "c":false}]') WITH(a INT '$.a', b VARCHAR(30) '$.b', c BIT '$.c') t; text/html 2024-10-07T09:51:26+00:00 https://www.sqlines.com/sql-server-to-mariadb/outer_apply?rev=1728294686&do=diff SQL Server OUTER APPLY clause allows you to perform a correlated outer join (lateral join) often using a table-valued function or subquery. Correlated means that the right operand of a join clause contains a reference to the left operand. In MariaDB you can use LEFT OUTER JOIN LATERAL ON TRUE clause (still in progress, see <https://jira.mariadb.org/browse/MDEV-19078>). . text/html 2024-11-06T11:20:16+00:00 https://www.sqlines.com/sql-server-to-mariadb/plus_string_concat?rev=1730892016&do=diff In SQL Server, you can use the plus (+) operator to concatenate two or more strings. In MariaDB, you can use the CONCAT function. If one of the operands is NULL, both the plus (+) operator in SQL Server, and the CONCAT function in MariaDB return the NULL result. Note that when used for a string and a number, the plus (+) operator performs addition, not string concatenation in SQL Server. text/html 2025-01-09T08:58:28+00:00 https://www.sqlines.com/sql-server-to-mariadb/primary_key_name?rev=1736413108&do=diff In SQL Server, you can specify a name for a primary key constraint in both CREATE TABLE and ALTER TABLE statements. In MariaDBL, you can also specify a constraint name for a primary key, but it is ignored with a warning, and the name 'PRIMARY' is used instead. text/html 2024-10-16T13:54:45+00:00 https://www.sqlines.com/sql-server-to-mariadb/returns_table?rev=1729086885&do=diff In SQL Server a user-defined function (UDF) can return a table (a table-valued function), so you can refer to the function in FROM clause. MariaDB does not support table-valued functions but you can use JSON arrays and JSON functions to implement them. text/html 2024-10-03T20:24:58+00:00 https://www.sqlines.com/sql-server-to-mariadb/select_for_json?rev=1727987098&do=diff In SQL Server, you can use SELECT FOR JSON PATH to generate JSON from the query result i.e. aggregate values from multiple rows into a single JSON array. Consider a sample table: CREATE TABLE colors (name VARCHAR(30), category CHAR(1)); -- Insert sample rows INSERT INTO colors VALUES ('Red', 'R'); INSERT INTO colors VALUES ('Blue', 'B'); INSERT INTO colors VALUES ('Green', 'G'); text/html 2022-12-08T21:33:54+00:00 https://www.sqlines.com/sql-server-to-mariadb/select_into_temp?rev=1670535234&do=diff In SQL Server you can use a SELECT INTO statement to create a temporary table based on the query results. In MariaDB you have to use CREATE TEMPORARY TABLE AS SELECT statement. SQL Server: -- Create a new temporary table SELECT 'A' AS c1 INTO #tmp; -- Query the temporary table SELECT * FROM #tmp; # A text/html 2026-01-14T13:33:46+00:00 https://www.sqlines.com/sql-server-to-mariadb/smalldatetime?rev=1768397626&do=diff In SQL Server, the SMALLDATETIME data type stores date and time values with minute-level accuracy (seconds are always 00). In MariaDB, you can use the DATETIME data type along with an expression to set the seconds to 00. SQL Server: -- Sample table CREATE TABLE products ( name VARCHAR(30), created_dt SMALLDATETIME ); INSERT INTO products VALUES ('Apple', GETDATE()); -- Seconds are set to 00 SELECT created_dt FROM products; /* 2026-01-14 16:28:00 */ text/html 2025-01-08T12:52:03+00:00 https://www.sqlines.com/sql-server-to-mariadb/smallmoney?rev=1736340723&do=diff In SQL Server, the SMALLMONEY data type stores 32-bit monetary values, decimal numbers with up to 4 digits after the decimal point. In MariaDB, you can use the DECIMAL(10,4) data type. SQL Server: -- Sample table CREATE TABLE products ( name VARCHAR(30), price SMALLMONEY ); -- Sample data INSERT INTO products VALUES ('Apple', 19.95); text/html 2026-01-22T20:14:29+00:00 https://www.sqlines.com/sql-server-to-mariadb/stuff?rev=1769112869&do=diff In SQL Server, the STUFF function inserts a string into another string at a specified position, deleting a specified number of characters. In MariaDB you can use the INSERT function. See also STUFF and SELECT FOR XML PATH conversion. SQL Server: text/html 2026-01-27T08:31:54+00:00 https://www.sqlines.com/sql-server-to-mariadb/stuff_select_for_xml?rev=1769502714&do=diff In SQL Server, STUFF with SELECT FOR XML PATH statement is widely used to concatenate strings from multiple rows into a single row value. Consider a sample table: CREATE TABLE colors (name VARCHAR(30)); -- Insert sample rows INSERT INTO colors VALUES ('Green'); INSERT INTO colors VALUES ('White'); INSERT INTO colors VALUES ('Blue'); text/html 2025-03-18T11:50:19+00:00 https://www.sqlines.com/sql-server-to-mariadb/try_catch?rev=1742298619&do=diff In SQL Server, you can use BEGIN TRY and BEGIN CATCH blocks to handle exceptions. In MariaDB, you can use the DECLARE EXIT HANDLER FOR SQLEXCEPTION. Consider a sample table with a unique constraint: -- Sample table CREATE TABLE colors (name VARCHAR(30) PRIMARY KEY NOT NULL); text/html 2024-10-24T06:07:34+00:00 https://www.sqlines.com/sql-server-to-mariadb/year?rev=1729750054&do=diff In SQL Server and MariaDB, the YEAR function extracts the year number from the datetime. SQL Server: -- Get the year SELECT YEAR('2024-10-24'); # 2024 MariaDB: -- Get the year SELECT YEAR('2024-10-24'); # 2024 For more information, see SQL Server to MariaDB Migration.