Pgloader: not supported type SYB-MSDATETIME2

[hesampour]

I want to convert mssql to postgre sql. but I get this error for datetime2 conversion

not supported type SYB-MSDATETIME2

[hesampour]

Is there any way to exclude datetime2 type?

[andnig]

Hey guys, I'm experiencing the same issue at the moment.
Any ideas for a workaround?

[efnineio]

same problem here. Fix above doesn't work by itself. Only "hack" i can come up with is to do as above, but also add

CAST type datetime2 to text using remove-null-characters

that will force the date into a string (specifying it as a string).

that will drop the date in as a text field in the format
"Jun 3 2019 11:27:09:0700000AM"

IF i don't use the transform function, it will try to cast it as a timestamp as that string. "invalid input syntax for type timestamp with time zone: "Jun 3 2019 11:27:09:0700000AM" Not sure if it's a server issue

If someone could write a LISP transform function that can take that string and cast it back into date, that'd probably work.

[semenmiroshnichenko]

Yes, I forgot to mention it: with my PR you still need to cast datatime2 explicitly. We did it by
cast type datetime2 to timestamptz, and it works.

[efnineio]

Interesting, when i tried that i get the error as such:

"ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "Jun 3 2019 11:27:09:0700000AM""

i'm wondering why the datetime2 is coming out in that format, rather than the typical "yyyy-mm-dd" format. Maybe a server side setting for default date format?

[semenmiroshnichenko]

But was you able to build pgloader with our patch from PR above or you fixed it only by adding cast?

[gjvoosten]

Interesting, when i tried that i get the error as such:

"ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "Jun 3 2019 11:27:09:0700000AM""

i'm wondering why the datetime2 is coming out in that format, rather than the typical "yyyy-mm-dd" format. Maybe a server side setting for default date format?

@efnineio The date format can be changed in FreeTDS' locales.conf; e.g. for me I had to change the default (note that for FreeTDS, %z means something different from strftime, see https://www.freetds.org/userguide/locales.htm ):

[default]
        #date format = %b %e %Y %I:%M:%S:%z%p
        date format = %Y-%m-%d %H:%M:%S.%z

Then, with both the patch ( https://github.com/dimitri/pgloader/pull/1036 ) and the cast, it worked for me.

[efnineio]

Ah yes, i didn't think about freetds settings.
Worked for me! (on a modified docker image from this repository)
To sum up 3 steps made this
1) add the ":syb-msdatetime2" to the mssql.lisp file per the pull request above
2) explicitly cast the type "cast type datetime2 to timestamptz" in the command file
3) add a file /etc/freetds/locales.conf with the lines
[default]
date format = %Y-%m-%d %H:%M:%S.%z

That got it to work for me. thank you!