how to get excel to treat a date as a date not a string when doing CopyFromRecordset

Question

I have an SQL query from SQL Server which returns dates as a string in the format "YYYY-MM-DD". If I enter a date in this format into a cell, it's recognised as a date. But when I populate a worksheet with CopyFromRecordset, it seems to be treated as a string. Any formula which uses the cell converts it to a date first. For example, if my dates are in col A and I make a new column B filled with a formula =A1 + 0 the formula returns my date, as a date.

The problem: I use the Recordset data for a few things, one of them being a pivot table. The pivot table does not see my dates as dates. I can't group as dates, for example. My hack is to make a new column which is basically =A1 + 0 I'm going to change my macro to automate this adding a zero, but I wonder if there's a way to get it right from the moment the CopyFromRecordset is performed.

Answer 1

The easiest way would be to do the conversion on the SQL server e.g.

SELECT CAST(date_text AS DATE) FROM TestExcelDates;

Answer 2

CopyFromRecordset is well known for causing data type / cell formatting issues in Excel.

I think I remember reading somewhere this is because the datatype of the recordset is ignored and Excel attempts to work out the format of each column itself based on a subset of the data in the recordset.

The best way round this is to set the cell formatting in the destination range before performing the CopyFromRecordset.