Differences

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

sql-server-to-mariadb:convert_string [April 05, 2017 11:05 pm] (current)
sqlines created
Line 1: Line 1:
 +====== CONVERT Datetime to String - SQL Server to MariaDB Migration ======
 +
 +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**:
 +
 +<code sql>
 +  -- 3rd parameter specifies 112 style (Date 'YYYYMMDD' format)
 +  SELECT CONVERT(CHAR(8), GETDATE(), 112);
 +  # 20170406
 +</code>
 +
 +**MySQL**:
 +
 +<code sql>
 +  -- Specify string format using format specifiers
 +  SELECT DATE_FORMAT(NOW(), '%Y%m%d');
 +  # 20170406
 +</code>
 +
 +
 +===== Mapping SQL Server Datetime Style to MariaDB Format =====
 +
 +When you convert CONVERT function to DATE_FORMAT you have to map the SQL Server style to the appropriate format string in MariaDB:
 +
 +| **SQL Server Style** || **MariaDB Format String** | **Output Example** |
 +| 101 | US - MM/DD/YYYY | '%m/%d/%Y' | 11/29/2012 |
 +| 103 | British/French - DD/MM/YYYY | '%d/%m/%Y' | 29/11/2012 |
 +| 108 | Time - HH:MI:SS | '%T' | 18:21:11 |
 +| 112 | Date - YYYYMMDD | '%Y%m%d' | 2017-04-06 |
 +| 121 | ODBC - YYYY-MM-DD HH:MI:SS.FFF | '%Y-%m-%d %T.%f' | 2012-11-29 18:21:11.123 |
 +| 20 | ODBC - YYYY-MM-DD HH:MI:SS | '%Y-%m-%d %T' | 2012-11-29 18:21:11 |
 +
 +Conversion examples:
 +
 +| **SQL Server** | **MariaDB** |
 +| CONVERT(VARCHAR, GETDATE(), 101) | DATE_FORMAT(NOW(), '%m/%d/%Y') |
 +| CONVERT(VARCHAR, GETDATE(), 103) | DATE_FORMAT(NOW(), '%d/%m/%Y') |
 +| CONVERT(VARCHAR, GETDATE(), 108) | DATE_FORMAT(NOW(), '%T') |
 +| CONVERT(VARCHAR, GETDATE(), 112) | DATE_FORMAT(NOW(), '%Y%m%d') |
 +| CONVERT(VARCHAR, GETDATE(), 121) | DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f') |
 +| CONVERT(VARCHAR, GETDATE(), 20) | DATE_FORMAT(NOW(), '%Y-%m-%d %T') |
 +
 +===== Database and SQL Migration Tools =====
 +
 +  * [[/sql-server-to-mariadb|SQL Server to MariaDB Migration Tools and Reference]]
 +
 +~~NOTOC~~