I'm pulling data to use in a Pivot table in Excel, but Excel is not recognizing the date format.
我在Excel中的数据透视表中提取数据，但Excel无法识别日期格式。
I'm using..
我在用着..
CONVERT(date, [DateTimeSelected]) AS [Search Date]
Which shows as as 2013-08-01 in my query output.
在我的查询输出中显示为2013-08-01。
When it get to into my Excel pivot table via my SQL Connection it looks the same but the filters do not recognize as a date.
当它通过我的SQL连接进入我的Excel数据透视表时它看起来相同，但过滤器不能识别为日期。
Here you can see how Excel is see it as text on the left and n the right what it should look like in Excel when it recognizes it as a date.
在这里，您可以看到Excel如何将其视为左侧的文本，右侧是Excel在将其识别为日期时应该在Excel中看起来的样子。
Any ideas please?
有什么想法吗？
Thanks ;-)
谢谢 ;-）
Tried all these but only the original B.Depart (date time) comes through as a date, none of the converted columns are read by Excel as a date...
尝试了所有这些，但只有原始的B.Depart（日期时间）作为日期出现，Excel中没有任何转换的列作为日期读取...
I get loads of formats but Excel must not like converted dates??
我得到大量的格式，但Excel必须不喜欢转换日期？
B.Depart AS 'Holiday Date time',
CONVERT(VARCHAR(10), B.Depart,103) AS 'Holiday Date',
DATENAME(weekday, B.Depart) AS 'Holiday Day Name',
CONVERT(CHAR(2), B.Depart, 113) AS 'Holiday Day',
CONVERT(CHAR(4), B.Depart, 100) AS 'Holiday Month',
CONVERT(CHAR(4), B.Depart, 120) AS 'Holiday Year',
CONVERT(VARCHAR(10),B.Depart,10) AS 'New date',
CONVERT(VARCHAR(19),B.Depart),
CONVERT(VARCHAR(10),B.Depart,10),
CONVERT(VARCHAR(10),B.Depart,110),
CONVERT(VARCHAR(11),B.Depart,6),
CONVERT(VARCHAR(11),B.Depart,106),
CONVERT(VARCHAR(24),B.Depart,113)
4 个解决方案
#1
7
Is appears that cast(convert(char(11), Bo.Depart, 113) as datetime) works..
似乎cast（转换（char（11），Bo.Depart，113）作为datetime）工作..
#2
2
I found the only answer for me was to set the SQL data as source for a Table, rather than a pivot table, and then build a pivot table from that Table.
我发现对我来说唯一的答案是将SQL数据设置为Table的源，而不是数据透视表，然后从该表构建数据透视表。
That was I was able to apply the Number format to the Pivot table Row Labels, and that allowed me to format the date how I wanted on the chart.
那是我能够将数字格式应用到数据透视表行标签，这使我能够在图表上格式化我想要的日期。
#3
0
I had the same problem using Excel 2010. My solution was to specify the connection type to the SQL Server as 'SQL Server Native Client 10.0' rather than 'SQL Server' (available in the advanced/other selection in the data connection guide). Using that everything worked fine connecting to sql server tables containing standard date formatted columns.
我使用Excel 2010时遇到了同样的问题。我的解决方案是将SQL Server的连接类型指定为“SQL Server Native Client 10.0”而不是“SQL Server”（可在数据连接指南中的高级/其他选项中找到）。使用它一切正常连接到包含标准日期格式列的SQL Server表。
#4
0
Important to note that if the dates are first recognized as text, and then you change the query to one of the suggestions, you'll have to tell Excel to not retain the old text-formatted values in the Pivot Table cache (Pivot tables by default retain 'old' values in each field in the cache in the background, even if they're not in the data currently being returned). Otherwise, since the first values are still in the cache (even though they are no longer in the live data), the whole field gets recognized as text.
需要注意的是，如果首先将日期识别为文本，然后将查询更改为其中一个建议，则必须告知Excel不要在数据透视表缓存中保留旧的文本格式值（透视表格默认情况下，在后台缓存中的每个字段中保留“旧”值，即使它们不在当前正在返回的数据中。否则，由于第一个值仍在缓存中（即使它们不再存在于实时数据中），整个字段也会被识别为文本。
For example, I had an existing table where I was having this problem. I changed my query to use Mike's post of
例如，我有一个现有的表，我遇到了这个问题。我改变了我的查询以使用Mike的帖子
cast(convert(char(11), Bo.Depart, 113) as datetime)
cast（convert（char（11），Bo.Depart，113）as datetime）
, but it didn't actually result in Excel recognizing it as a date until after I reset the items retained in the Pivot Table cache.
，但它实际上并没有导致Excel将其识别为日期，直到我重置数据透视表缓存中保留的项目。
To change how the Pivot Table retains items in the Cache, 1. Right-click on the Table 2. Choose 'Pivot Table Options' 3. On 'Data' tab, change drop-down menu for 'Number of items to retain per field' to 'None'.
若要更改数据透视表保留缓存中项目的方式，1。右键单击表2.选择“数据透视表选项”3.在“数据”选项卡上，更改“每个字段要保留的项目数”的下拉菜单'到'无'。
This is also useful when your slicer buttons show values that are no longer in the data set.
当切片器按钮显示不再位于数据集中的值时，这也很有用。
#1
7
Is appears that cast(convert(char(11), Bo.Depart, 113) as datetime) works..
似乎cast（转换（char（11），Bo.Depart，113）作为datetime）工作..
#2
2
I found the only answer for me was to set the SQL data as source for a Table, rather than a pivot table, and then build a pivot table from that Table.
我发现对我来说唯一的答案是将SQL数据设置为Table的源，而不是数据透视表，然后从该表构建数据透视表。
That was I was able to apply the Number format to the Pivot table Row Labels, and that allowed me to format the date how I wanted on the chart.
那是我能够将数字格式应用到数据透视表行标签，这使我能够在图表上格式化我想要的日期。
#3
0
I had the same problem using Excel 2010. My solution was to specify the connection type to the SQL Server as 'SQL Server Native Client 10.0' rather than 'SQL Server' (available in the advanced/other selection in the data connection guide). Using that everything worked fine connecting to sql server tables containing standard date formatted columns.
我使用Excel 2010时遇到了同样的问题。我的解决方案是将SQL Server的连接类型指定为“SQL Server Native Client 10.0”而不是“SQL Server”（可在数据连接指南中的高级/其他选项中找到）。使用它一切正常连接到包含标准日期格式列的SQL Server表。
#4
0
Important to note that if the dates are first recognized as text, and then you change the query to one of the suggestions, you'll have to tell Excel to not retain the old text-formatted values in the Pivot Table cache (Pivot tables by default retain 'old' values in each field in the cache in the background, even if they're not in the data currently being returned). Otherwise, since the first values are still in the cache (even though they are no longer in the live data), the whole field gets recognized as text.
需要注意的是，如果首先将日期识别为文本，然后将查询更改为其中一个建议，则必须告知Excel不要在数据透视表缓存中保留旧的文本格式值（透视表格默认情况下，在后台缓存中的每个字段中保留“旧”值，即使它们不在当前正在返回的数据中。否则，由于第一个值仍在缓存中（即使它们不再存在于实时数据中），整个字段也会被识别为文本。
For example, I had an existing table where I was having this problem. I changed my query to use Mike's post of
例如，我有一个现有的表，我遇到了这个问题。我改变了我的查询以使用Mike的帖子
cast(convert(char(11), Bo.Depart, 113) as datetime)
cast（convert（char（11），Bo.Depart，113）as datetime）
, but it didn't actually result in Excel recognizing it as a date until after I reset the items retained in the Pivot Table cache.
，但它实际上并没有导致Excel将其识别为日期，直到我重置数据透视表缓存中保留的项目。
To change how the Pivot Table retains items in the Cache, 1. Right-click on the Table 2. Choose 'Pivot Table Options' 3. On 'Data' tab, change drop-down menu for 'Number of items to retain per field' to 'None'.
若要更改数据透视表保留缓存中项目的方式，1。右键单击表2.选择“数据透视表选项”3.在“数据”选项卡上，更改“每个字段要保留的项目数”的下拉菜单'到'无'。
This is also useful when your slicer buttons show values that are no longer in the data set.
当切片器按钮显示不再位于数据集中的值时，这也很有用。