Unable to successfully read sqlserver Date datatype [ODBC, DataFrames]

New to Julia
,
1

i have a very simple query:

using ODBC, Dates, DataFrames

query = "select id, convert(date, ColumnName1) as CalendarDate from table1"
conn = DBInterface.connect(ODBC.Connection, "ConnectionString");
statement = DBInterface.prepare(conn, query);
df = DBInterface.execute(statement) |> DataFrame

The above code returns CalendarDate as type String? but the string values look like this:

"2000-01-1\0\0"

The date is being incorrectly read. I am not sure what the \0s are and how to fix this?

2

What is stored in ColumnName1?
How about reading the raw data from that column and converting it in Julia to a date?

What database are you using?

I am not very familiar with databases, but the two queries below work fine for me. I would expect them to run on ODBC just as well.
But your call convert(date, ColumnName1) is inverted compared to the syntax that I am using on a mysql database. Can you confirm that this is intentional?

conn = DBInterface.connect(MySQL.Connection, mysqlhostip, mysqluser, mysqlpassword;port=mysqlport)
DBInterface.execute(conn,"USE mysql;") #use database_name

julia> rs = DBInterface.execute(conn,"SELECT datetime from $(tblName);") |> DataFrame
5660×1 DataFrame
│ Row  │ datetime            │
│      │ DateTime            │
├──────┼─────────────────────┤
│ 1    │ 2020-07-21T07:49:00 │
│ 2    │ 2020-07-21T07:52:00 │
│ 3    │ 2020-07-21T07:53:00 │
│ 4    │ 2020-07-21T08:02:00 │
│ 5    │ 2020-07-21T08:05:00 │
│ 6    │ 2020-07-21T08:07:00 │
│ 7    │ 2020-07-21T08:38:00 │
│ 8    │ 2020-07-21T08:51:00 │
⋮
│ 5652 │ 2021-10-20T19:24:00 │
│ 5653 │ 2021-10-20T19:25:00 │
│ 5654 │ 2021-10-20T19:27:00 │
│ 5655 │ 2021-10-20T19:29:00 │
│ 5656 │ 2021-10-20T19:38:00 │
│ 5657 │ 2021-10-20T19:43:00 │
│ 5658 │ 2021-10-20T19:58:00 │
│ 5659 │ 2021-10-20T20:01:00 │
│ 5660 │ 2021-10-20T20:06:00 │

julia> rs = DBInterface.execute(conn,"SELECT convert(datetime,date) as foo from $(tblName);") |> DataFrame
5660×1 DataFrame
│ Row  │ foo        │
│      │ Date?      │
├──────┼────────────┤
│ 1    │ 2020-07-21 │
│ 2    │ 2020-07-21 │
│ 3    │ 2020-07-21 │
│ 4    │ 2020-07-21 │
│ 5    │ 2020-07-21 │
│ 6    │ 2020-07-21 │
│ 7    │ 2020-07-21 │