SQL Date Formatting for Excel PivotTables

Question

I am retrieving data for use in a pivot table in Excel, but Excel does not recognize the date format.

I use..

CONVERT(date, [DateTimeSelected]) AS [Search Date] 

which shows as 2013-08-01 in my request.

When it gets to my Excel pivot table through my SQL connection, it looks the same, but the filters are not recognized as a date.

Here you can see how Excel sees it as text on the left and right, what it should look like in Excel when it recognizes it as a date.

Any ideas please?

Thanks; -)

I tried all this, but only the original B.Depart (time date) comes as a date, none of the converted columns is read by Excel as a date ...

I get many formats, but Excel doesn't have to convert dates

  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) 

Answer 1

It appears that cast(convert(char(11), Bo.Depart, 113) as datetime) works.

Answer 2

I found the only answer for me: set the SQL data as the source of the table, not the pivot table, and then create the pivot table from this table.

It was that I was able to apply the Number format to the Row shortcuts of the Pivot table, and this allowed me to format the date as I would like on a chart.

Answer 3

I had the same problem using Excel 2010. My solution was to specify the connection type on SQL Server as "SQL Server 10.0 native client" and not "SQL Server" (available in the advanced / other choice in the connection guide data). Using this, everything worked perfectly, connecting to sql server tables containing standard columns with date formatting.

Answer 4

It is important to note that if the dates are first recognized as text, and then you change the query to one of the sentences, you need to say that Excel will not save the old text values ​​in the cache of the pivot table (Pivot tables by default save the "old" values ​​in each field in cache in the background, even if they are not in the returned data). Otherwise, since the first values ​​are still in the cache (even if they are no longer in live data), the entire field is recognized as text.

For example, I had an existing table where I had this problem. I changed my request to use Mike's post

cast (convert (char (11), Bo.Depart, 113) as datetime)

but in reality this did not cause Excel to recognize it as a date until I reset the items stored in the cache of the pivot table.

To change how the pivot table saves items in the cache, 1. Right-click on the table 2. Select PivotTable Options 3. In the Data tab, change the "Number of items to save per field" drop-down menu to " Not".

This is also useful when your slicer buttons show values ​​that are no longer in the data set.

Answer 5

You can use the SmallDatetime format in your SQL table and will work. Never convert dates like varchar.