SQL Server CONVERT Datetime to String in MySQL

In SQL Server, you can use CONVERT function to convert a DATETIME value to a string with the specified format. In MySQL, you can use DATE_FORMAT function.

SQL Server:

  -- 3rd parameter specifies 121 style (ODBC 'YYYY-MM-DD HH:MI:SS.FFF' format with milliseconds)
  SELECT CONVERT(VARCHAR, GETDATE(), 121);
  # 2012-11-29 19:18:41.863

MySQL:

  -- Specify string format using format specifiers
  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f');
  # 2012-11-29 19:18:41.000000

Mapping SQL Server Datetime Style to MySQL Format

When you convert CONVERT function to DATE_FORMAT you have to map the SQL Server style to the appropriate format string in MySQL:

SQL Server Style		MySQL 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
113	European - DD MON YYYY HH:MI:SS.FFF	'%d %b %Y %T.%f'	15 Jul 2022 21:24:51:707
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	MySQL
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(), 113)	DATE_FORMAT (NOW(), '%d %b %Y %T.%f')
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

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

You could leave a comment if you were logged in.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference

SQL Server CONVERT Datetime to String in MySQL

Mapping SQL Server Datetime Style to MySQL Format

Database and SQL Migration Tools

About SQLines