Issues with converting from ODBC.API.SQLDate to Date - can't do any operations on SQLDate

New to Julia
1

Hi,
I have a query which gives me a list of dates from PostgreSQL via ODBC. The array looks like this:
head(tmp_df)
6×11 DataFrames.DataFrame
│ Row │ Date │ 8748 │ 8903 │ 9668 │ 9671 │ 9690 │ 10016 │ 10790 │ 11146 │ 11150 │ 11151 │
├─────┼────────────┼──────┼──────┼───────┼───────┼──────┼───────┼───────┼───────┼───────┼───────┤
│ 1 │ 2006-01-31 │ 2.07 │ 0.0 │ 1.92 │ 2.05 │ 0.0 │ 3.7 │ 0.0 │ 6.24 │ 6.23 │ 2.88 │
│ 2 │ 2006-02-28 │ 1.21 │ 0.0 │ 1.63 │ -0.55 │ 0.0 │ 0.56 │ 0.0 │ 3.53 │ 3.55 │ 0.14 │
│ 3 │ 2006-03-31 │ 1.96 │ 0.0 │ 1.18 │ 1.72 │ 0.0 │ 0.57 │ 0.0 │ -0.82 │ -0.85 │ 2.15 │
│ 4 │ 2006-04-30 │ 0.85 │ 0.0 │ -0.02 │ 0.75 │ 0.0 │ -0.86 │ 0.0 │ 0.61 │ 0.57 │ 2.32 │
│ 5 │ 2006-05-31 │ 0.69 │ 0.0 │ 3.66 │ 2.91 │ 0.0 │ 0.66 │ 0.0 │ -7.74 │ -8.11 │ -1.71 │
│ 6 │ 2006-06-30 │ 0.05 │ 0.0 │ 0.29 │ 0.57 │ 0.0 │ -0.44 │ 0.0 │ 6.21 │ 6.2 │ 1.94 │

the typeof(tmp_df[:Date]) is DataArrays.DataArray{ODBC.API.SQLDate,1}

I can’t seem to convert it to Date format to do any operations on:
convert(Date,tmp_df[:Date])
ERROR: MethodError: Cannot convert an object of type DataArrays.DataArray{ODBC.API.SQLDate,1} to a
n object of type Date
This may have arisen from a call to the constructor Date(…),
since type constructors fall back to convert methods

tmp_df[:Date]+Dates.Day(1)
ERROR: MethodError: no method matching +(::DataArrays.DataArray{ODBC.API.SQLDate,1}, ::Base.Dates.Da
y)

How can this be fixed? Thank you in advance

2

I don’t know if it is best way, but you could try this:

Julia> import ODBC
Julia> arr = [ODBC.API.SQLDate(2018, 2,i) for i in 1:3]  # create example array 
3-element Array{ODBC.API.SQLDate,1}:
 2018-02-01
 2018-02-02
 2018-02-03

julia> [Date(i.year, i.month, i.day) for i in arr]  # new array with Date type elements
3-element Array{Date,1}:
 2018-02-01
 2018-02-02
 2018-02-03
3

mp_df[:Date] refers to a column of the data frame, which is an array object. You cannot convert it to a Date, but possibly to an Array{Date}, for example using convert(Array{Date}, mp_df[:Date]), Date.(mp_df[:Date]) (untested), or the more explicit form @Liso proposed.

4

I ran into the same issue and ended up creating a function to handle the problem.

function odttodt(odt::Union{Missings.Missing, ODBC.API.SQLTimestamp})::Union{Missings.Missing, DateTime}
	ismissing(odt) && return odt
	return DateTime(odt.year, odt.month, odt.day, odt.hour, odt.minute, odt.second, (odt.fraction / 1000000))
end		# odttodt

	ctrdf[:RunDT] = missings(DateTime, size(ctrdf, 1))
	ctrdf[:RunDT] .= odttodt.(ctrdf[:tRunDT])

As you can see above, I could then use the dot syntax to convert an entire dataframe column. Note that I use datetime format and had to handle the difference between milliseconds, microseconds, and nanoseconds in the different formats.

A simpler variant of this would handle going to Date format for your use case.