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.

Answers


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.

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.


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...


Need Your Help

Is it possiable to have the detailTextLabel automaticly resize the font?

iphone objective-c uitableview

I'm using the UITableViewCellStyleValue1 for my table cell style. That's the one that puts the textLabel on the left, left aligned, and the detailTextLabel on the right, right aligned.

automated call from skype account via C# application (using Skype4COM.dll)

c# asp.net skype4com

We are trying to place a mobile call via Skype API(Skype4COM.dll) in C# application. We found a sample code in one of the website. The code is as follows.The code is throwing an error at line "skype.