Differences

This shows you the differences between two versions of the page.

sql-server-to-mariadb:convert_datetime [April 07, 2017 12:12 am] (current)
sqlines created
Line 1: Line 1:
 +====== SQL Server CONVERT for Datetime in MariaDB ======
 +
 +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. {{:exclamation.png|}}
 +
 +**SQL Server**:
 +
 +<code sql>
 +  -- 3rd parameter specifies 121 style (ODBC 'YYYY-MM-DD HH:MI:SS.FFF' format with milliseconds)
 +  SELECT CONVERT(DATETIME, '2012-11-29 18:21:11.123', 121);
 +  # 2012-11-29 18:21:11.123
 +  
 +  SELECT CONVERT(DATETIME, GETDATE());
 +  # 2017-04-07 09:55:40.550
 +</code>
 +
 +**MariaDB**:
 +
 +<code sql>
 +  -- Specify string format using format specifiers
 +  SELECT STR_TO_DATE('2012-11-29 18:21:11.123', '%Y-%m-%d %T.%f');
 +  # 2012-11-29 18:21:11.123000
 +  
 +   SELECT CONVERT(NOW(), DATETIME);
 +   # 2017-04-07 09:55:40
 +</code>
 +
 +
 +===== Mapping SQL Server Datetime Style to MariaDB Format =====
 +
 +When you convert CONVERT function to STR_TO_DATE you have to map the SQL Server style to the appropriate format string in MariaDB:
 +
 +| **SQL Server Style** || **MariaDB Format String** | **String Example** |
 +| 101 | US - MM/DD/YYYY | '%m/%d/%Y' | '11/29/2012' |
 +| 121 | ODBC - YYYY-MM-DD HH:MI:SS.FFF | '%Y-%m-%d %T.%f' | '2012-11-29 18:21:11.123' |
 +
 +Conversion examples:
 +
 +| **SQL Server** | **MariaDB** |
 +| CONVERT(DATETIME, '11/29/2012', 101) | STR_TO_DATE('11/29/2012', '%m/%d/%Y') |
 +| CONVERT(DATETIME, '2012-11-29 18:21:11.123', 121) | STR_TO_DATE('2012-11-29 18:21:11.123', '%Y-%m-%d %T.%f') |
 +
 +===== SQL Server CONVERT for SMALLDATETIME in MariaDB =====
 +
 +In SQL Server SMALLDATETIME data type stores a datetime value with 00 seconds. You can use the expression below to keep 00 seconds after using the CONVERT function in MariaDB:
 +
 +**SQL Server**:
 +
 +<code sql>
 +  -- SMALLDATETIME is always with 00 seconds
 +  SELECT CONVERT(SMALLDATETIME, GETDATE());
 +  # 2017-04-07 10:05:00
 +</code>
 +
 +**MariaDB**:
 +
 +<code sql>
 +   SELECT CONVERT(DATE_FORMAT(NOW(), '%Y-%m-%d %H-%i-00'), DATETIME);
 +   # 2017-04-07 10:05:00
 +</code>
 +
 +
 +
 +===== Database and SQL Migration Tools =====
 +
 +  * [[/sql-server-to-mariadb|SQL Server to MariaDB Migration Tools and Reference]]
 +