CONVERT String to Datetime - Sybase ASE to MariaDB Migration

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

Sybase ASE:

  -- 3rd parameter specifies 16 style (mon dd yyyy hh24:mm:ss)
  SELECT CONVERT(DATETIME, 'Dec 14 2017 18:48:13', 16);

MariaDB:

  -- Specify string format using format specifiers
  SELECT STR_TO_DATE('Dec 14 2017 18:48:13', '%b %d %Y %H:%i:%s');

Mapping Sybase ASE Datetime Style to MariaDB Format

You can use SQLines SQL Converter to convert Sybase ASE CONVERT function to STR_TO_DATE 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(DATETIME, 'Dec 14, 2017', 107)	SELECT STR_TO_DATE('Dec 14, 2017', '%b %d, %Y');
2	SELECT CONVERT(DATETIME, '2017.12.14', 102)	SELECT STR_TO_DATE('2017.12.14', '%Y.%m.%d');
3	SELECT CONVERT(DATETIME, '18:48:13:970', 20)	SELECT STR_TO_DATE('18:48:13:970', '%H:%i:%s:%f');

For more information, see:

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications

CONVERT String to Datetime - Sybase ASE to MariaDB Migration

Mapping Sybase ASE Datetime Style to MariaDB Format