CONVERT Datetime to String - Sybase ASE to MariaDB Migration

In Sybase ASE you can use CONVERT function to convert a DATETIME value to a string with the specified format. In MariaDB, you can use DATE_FORMAT function:

Sybase ASE:

  -- 3rd parameter specifies 140 style ('YYYY-MM-DD HH:MI:SS.FFFFFF' format with microseconds)
  SELECT CONVERT(VARCHAR, GETDATE(), 140);
  # 2017-12-14 18:40:41.063000

MariaDB:

  -- Specify string format using format specifiers
  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f');
  # 2017-12-14 18:40:41.000000

Mapping Sybase ASE Datetime Style to MariaDB Format

You can use SQLines SQL Converter to convert Sybase ASE CONVERT function to DATE_FORMAT that maps the style to the appropriate format string in MariaDB:

Sybase ASE		MariaDB	Example
0 or 100	Default - mon dd yyyy hh:mmAM/PM	'%b %d %Y %h:%i%p'	Dec 14 2017 6:48PM
1	US - mm/dd/yy	'%m/%d/%y'	12/14/17
101	US - mm/dd/yyyy	'%m/%d/%Y'	12/14/2017
2	yy.mm.dd	'%y.%m.%d'	17.12.14
102	yyyy.mm.dd	'%Y.%m.%d'	2017.12.14
3	English/French - dd/mm/yy	'%d/%m/%y'	14/12/17
103	English/French - dd/mm/yyyy	'%d/%m/%Y'	14/12/2017
4	German - dd/mm/yy	'%d.%m.%y'	14.12.17
104	German - dd/mm/yyyy	'%d.%m.%Y'	14.12.2017
5	dd-mm-yy	'%d-%m-%y'	14-12-17
105	dd-mm-yyyy	'%d-%m-%Y'	14-12-2017
6	dd mon yy	'%d %b %y'	14 Dec 17
106	dd mon yyyy	'%d %b %Y'	14 Dec 2017
7	mon dd, yy	'%b %d, %y'	Dec 14, 17
107	mon dd, yyyy	'%b %d, %Y'	Dec 14, 2017
8 or 108	hh24:mm:ss	'%H:%i:%s'	18:48:13
9 or 109	mon dd yyyy hh:mm:ss:millisecAM/PM	'%b %d %Y %h:%i:%s:%f%p'	Dec 14 2017 6:48:13:970PM
10	US - mm-dd-yy	'%m-%d-%y'	12-14-17
110	US - mm-dd-yyyy	'%m-%d-%Y'	12-14-2017
11	Japan - yy/mm/dd	'%y/%m/%d'	17/12/14
111	Japan - yyyy/mm/dd	'%Y/%m/%d'	2017/12/14
12	ISO - yymmdd	'%y%m%d'	171214
112	ISO - yyyymmdd	'%Y%m%d'	20171214
13	yy/dd/mm	'%y/%d/%m'	17/14/12
113	yyyy/dd/mm	'%Y/%d/%m'	2017/14/12
14	mm/yy/dd	'%m/%y/%d'	12/17/14
114	mm/yyyy/dd	'%m/%Y/%d'	12/2017/14
15	dd/yy/mm	'%d/%y/%m'	14/17/12
115	dd/yyyy/mm	'%d/%Y/%m'	14/2017/12
16 or 116	mon dd yyyy hh24:mm:ss	'%b %d %Y %H:%i:%s'	Dec 14 2017 18:48:13
17 or 117	hh:mmAM/PM	'%h:%i%p'	6:48PM
18 or 118	hh24:mm	'%H:%i'	18:48
19	hh:mm:ss:millisecAM/PM	'%h:%i:%s:%f%p'	6:48:13:970PM
20	hh24:mm:ss:millisec	'%H:%i:%s:%f'	18:48:13:970
21	yy/mm/dd hh24:mm:ss	'%y/%m/%d %H:%i:%s'	17/12/14 18:48:13
22	yy/mm/dd hh:mmAM/PM	'%y/%m/%d %h:%i%p'	17/12/14 6:48PM
23	yyyy-mm-ddThh24:mm:ss	'%Y-%m-%dT%H:%i:%s'	2017-12-14T18:48:13
36 or 136	hh:mm:ss.microsecAM/PM	'%h:%i:%s.%f%p'	6:48:13.970000PM
37 or 137	hh24:mm:ss.microsec	'%H:%i:%s.%f'	18:48:13.970000
38	mon dd yy hh:mm:ss.microsecAM/PM	'%b %d %y %h:%i:%s.%f%p'	Dec 14 17 6:48:13.970000PM
138	mon dd yyyy hh:mm:ss.microsecAM/PM	'%b %d %Y %h:%i:%s.%f%p'	Dec 14 2017 6:48:13.970000PM
39	mon dd yy hh24:mm:ss.microsec	'%b %d %y %H:%i:%s.%f'	Dec 14 17 18:48:13.970000
139	mon dd yyyy hh24:mm:ss.microsec	'%b %d %Y %H:%i:%s.%f'	Dec 14 2017 18:48:13.970000
40	yy-mm-dd hh:mm:ss.microsec	'%y-%m-%d %H:%i:%s.%f'	17-12-14 18:48:13.970000
140	yyyy-mm-dd hh:mm:ss.microsec	'%Y-%m-%d %H:%i:%s.%f'	2017-12-14 18:48:13.970000

Conversion examples:

Sybase ASE		MariaDB
1	SELECT CONVERT(CHAR, GETDATE(), 0)	SELECT DATE_FORMAT(NOW(), '%b %d %Y %h:%i%p');
2	SELECT CONVERT(CHAR, GETDATE(), 102)	SELECT DATE_FORMAT(NOW(), '%Y.%m.%d');
3	SELECT CONVERT(CHAR, GETDATE(), 20)	SELECT DATE_FORMAT(NOW(), '%H:%i:%s:%f');

For more information, see:

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference

CONVERT Datetime to String - Sybase ASE to MariaDB Migration

Mapping Sybase ASE Datetime Style to MariaDB Format