I have data this is linked from SQL Server into an excel document. The column format on the SQL Server is datetime2.

When I get the data via an ODBC connection it comes across as a string?

I tried using

CAST(column AS DATE ) 

but that didn't work.

I tried reformatting via

CONVERT(VARCHAR(10), column, 103) 

as well but that didn't work.

I tried retrieving the data via Microsoft query as well but that didn't work.

At the moment I am using VBA code like:

While (ActiveCell.Value <> "")
  ActiveCell.Value = DATEVALUE(ActiveCell.Value)
  ActiveCell.Offset(1,0).Activate
Wend

and looping through each column that needs this treatment but 100000 rows in multiple columns takes forever to loop through. Are there any alternatives?