-1

I needed to move some large amount of data (100Ms of rows) from a Sybase database to a MySQL database.

One of the issues I ran into is the conversion of columns of type smalldatetime, datetime and bigdatetime into a format that MySQL will readily understand.

After much struggle and after almost giving up on finding a solution that wouldn't involve some Perl massaging of the datetime data, I found a very acceptable solution.

It's doesn't meet the full ISO 8601 standard, but it's very close and for my purpose, it works perfectly.

Jerome Provensal
  • 931
  • 11
  • 22

1 Answers1

2

The answer is that I found a secret (at least undocumented to my knowledge) Sybase convert style. The style is 140 and it yields this type of format output regardless of the datetime format:

select convert(char, convert(smalldatetime, getdate()), 140);
 2020-03-06 21:08:00.000000

select convert(char, convert(datetime, getdate()), 140);
 2020-03-06 21:08:21.173000

select convert(char, convert(bigdatetime, getdate()), 140);
 2020-03-06 21:08:30.532000

select convert(char, convert(bigdatetime, "2020-03-06 21:08:30.123456"), 140);
 2020-03-06 21:08:30.123456
Jerome Provensal
  • 931
  • 11
  • 22
  • Just for info style 109 (which is documented) gives you the same result. – Rich Campbell Mar 10 '20 at 07:11
  • 1
    @RichCampbell, unless I'm reading the doc wrong, 109 gives you: mon dd yyyy hh:mm:ss AM (or PM) which is quite different and for one thing doesn't have microsecs. See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X41864.htm – Jerome Provensal Mar 10 '20 at 13:49
  • 1
    you're right I could have sworn I'd replicated your output this morning but I can't do it again so I agree with your undocumented :) – Rich Campbell Mar 10 '20 at 14:52