SQL Date formatting for Excel pivot tables

Question

I'm pulling data to use in a Pivot table in Excel, but Excel is not recognizing the date format.

I'm using..

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

Which shows as as 2013-08-01 in my query output.

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.

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.

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

I get loads of formats but Excel must not like converted 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

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

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

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.