FORMAT - Convert Datetime to String - SQL Server to MySQL Migration

In SQL Server FORMAT function converts a datetime value to string using the specified format. In MySQL 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

MySQL:

  -- Convert the current date and time to string (year-month-day)
  SELECT DATE_FORMAT(NOW(), '%Y%m%d');
  # 20230215

FORMAT and DATE_FORMAT Format Specifiers

When you convert SQL Server FORMAT function to DATE_FORMAT function in MySQL, you have to map the format specifiers:

SQL Server FORMAT MySQL DATE_FORMAT
YYYY 4-digit year %Y
MM Month (1 - 12) %m
DD Day (1 - 31) %d
HH Hour (0 - 23) %H
hh Hour (1 - 12) %h
mm Minutes (0 - 59) %i
ss Seconds (0 - 59) %s

Conversion Examples

Typical conversion examples:

SQL Server MySQL
1 FORMAT(GETDATE(), 'yyyyMMdd' DATE_FORMAT(SYSDATE(), '%Y%m%d)
2 FORMAT(GETDATE(), 'yyyyMMddHHmmss' DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s')

For more information, see SQL Server to MySQL Migration.