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: