在执行CopyFromRecordset时，如何将excel视为日期而不是字符串 [英] how to get excel to treat a date as a date not a string when doing CopyFromRecordset

问题描述

我有一个来自SQL Server的SQL查询，它以YYYY-MM-DD格式的字符串返回日期。
如果我将此格式的日期输入单元格，则将其识别为日期。
但是当我使用CopyFromRecordset填充工作表时，它似乎被视为一个字符串。
使用该单元格的任何公式都将其转换为日期。例如，如果我的日期在列A中，并且我创建一个填充了公式= A1 + 0
的新列B，则该公式返回我的日期，作为日期。

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.

问题：
我使用Recordset数据的一些东西，其中一个是一个枢轴表。
数据透视表不会将日期视为日期。例如，我不能按日期分组。我的黑客是要做一个新的列，基本上是= A1 + 0
我要改变我的宏自动化这个添加一个零，但我想知道是否有一种方法，从CopyFromRecordset被执行。

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.

推荐答案

最简单的方法是在SQL服务器上进行转换。

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

SELECT CAST(date_text AS DATE) FROM TestExcelDates;

这篇关于在执行CopyFromRecordset时，如何将excel视为日期而不是字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！