how to get excel to treat a date as a date not a string when doing CopyFromRecordset
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.
The easiest way would be to do the conversion on the SQL server e.g.
SELECT CAST(date_text AS DATE) FROM TestExcelDates;
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.
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!
Use the CDate() function when populating cells with dates from the recordset. This will convert the string to a date value.
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.
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.
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...