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.

Have you tried to manually assign the format of the cell to a "DATE" data type before the copyfromrecordset is applied? — xQbert, Jun 03 '15 at 20:04

score 4 · Accepted Answer · answered Apr 27 '12 at 08:00

4

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

SELECT CAST(date_text AS DATE) FROM TestExcelDates;

answered Apr 27 '12 at 08:00

onedaywhen

55,269
12
100
138

Note: this did not work for me in Oracle, so I just had to change the column's number Format in Excel. – waternova May 19 '15 at 18:24
5

casting as date did not work for me on sql server. Datetime did the trick. – NiH Jul 07 '17 at 12:56

score 2 · Answer 2 · answered Apr 27 '12 at 15:32

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.

score 2 · Answer 3 · answered Jun 03 '15 at 20:00

I had this problem after I had changed a view on my SQL Server database. I had changed the data type to DATE; formerly it was on an older version which didn't support DATE so I had used DATETIME. I suspect Excel doesn't always recognize the Date datatype through the SQLOLEDB provider, but it does recognize DATETIME. The field of interest is meas_date. So I altered the view by changing this to a cast SELECT CAST(meas_date AS DATETIME) AS meas_date, ... and refreshed the query in Excel. Worked!

score 1 · Answer 4 · answered Apr 27 '12 at 06:24

Use the CDate() function when populating cells with dates from the recordset. This will convert the string to a date value.

Edit

That works for setting individual cell values. For using CopyFromRecordset I think you need to do the conversino in the SQL query, so the column returned by the query is a date type rather than a string.

score 0 · Answer 5 · answered Jan 15 '15 at 18:34

I had this problem too importing data from Teradata, and got around it by first formatting the date columns with NumberFormat = "m/d/yy h:mm;@" (24 hr date) then stepping through the date fields afterwards with VBA and doing ws.cells(iRow, iCol).value = ws.cells(iRow, iCol).value, it forces Excel to reevaluate the string into a date/time field.

score -1 · Answer 6 · answered Apr 27 '12 at 06:43

This probably will not be the answer but will surely helps you finding the right solution for your problem

String stringCellValue = myCell.toString();

here myCell has datatype as CELL which I've converted to String format. If u want it in desired Date format, then u can try this-

SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-DD");
myCellDate = sdf.parse(stringCellValue );

Hope it helps in solving your problem...

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

6 Answers6

Linked