Error when converting SQLTimestamp with milliseconds to DateTime

[JockLawrie]

Hi there,

I am extracting some data from MS SQL Server.
When retrieving datetime columns the resulting DataFrame column has type ODBC.API.SQLTimestamp, OK so far.
I try to convert the column to DateTime, and it works if the value doesn't have milliseconds.
If it has milliseconds I have to convert it to a String first, which is hacky.
Example below.

using Base.Dates

#val is 2018-02-12T00:26:49, with type ODBC.API.SQLTimestamp
DateTime(val)  # OK

#val is 2018-02-12T00:26:49.537, with type ODBC.API.SQLTimestamp
DateTime(val)  # Error
DateTime(string(val))  # OK

[ellisvalentiner]

The issue seems to be that milliseconds are only defined on the range 0-999 (see Dates/src/types.jl#L193)

One option is to truncate the extra precision

using ODBC
using Base.Dates

val = ODBC.API.SQLTimestamp(2018, 2, 12, 0, 26, 49, 537000000)
DateTime(x::ODBC.API.SQLTimestamp) = Base.Dates.DateTime(x.year, x.month, x.day, x.hour, x.minute, x.second, div(x.fraction, 1000000))
DateTime(val)

[omus]

You can simplify your logic if you change Int(trunc(x.fraction / 1000000, 0)) into div(x.fraction, 1000000)

[ValdarT]

Does anyone know what is the reason behind having the SQLDate, SQLTime, and SQLTimestamp structs?

These structs have a lot of missing methods and it would seem a lot more natural to return the Julia Base.Dates types rather than these custom ones...

[quinnj]

As of #263 , we now return Base Julia structs for query resultset columns.