Convert SyBase datetime to MySQL format with convert() and str_replace()

Background

When exporting a SyBase table to CSV with bcp, the default datetime format is Dec 24 2017 12:00:00:000PM. MySQL expects yyyy-mm-dd hh:nn:ss, so a direct import fails.

Solution Overview

The export can be made MySQL‑compatible by converting the SyBase datetime column to a formatted string before writing the CSV. This is done with SyBase’s convert() function (to produce yyyy/mm/dd and hh:nn:ss) and str_replace() (to replace the slashes with hyphens).

convert() Function

convert(datatype, expression, [format‑style])

casts expression to datatype. When datatype is a character type, format‑style controls the date/time representation. The two styles used are:

111 – yyyy/mm/dd 108 –

hh:nn:ss

str_replace() Function

str_replace(string1, string2, string3)

replaces every occurrence of string2 in string1 with string3. It is used to turn the slashes produced by convert(...,111) into hyphens.

Step‑by‑Step Implementation

Create a temporary table whose datetime column is defined as varchar(100).

create table test(id int, time datetime);
create table tmp(id int, time varchar(100));

Insert data from the original table, converting the datetime column:

insert into tmp
select id,
       str_replace(convert(varchar(100), time, 111), '/', '-')
       + ' ' + convert(varchar(100), time, 108)
from test;

Export the tmp table to CSV with bcp.

Import the CSV into the target MySQL table.

Result

The original value Dec 24 2017 12:00:00:000PM is transformed into 2017-12-24 12:00:00, which MySQL accepts without error.