Excel – Dates not recognized as dates in pivot table pulling directly from SQL Server

microsoft excelpivot tablesql server

My pivot pulls from an external data source with a date column. Excel doesn't see this column as a date and the 'Format Cells' option panel doesn't change how the dates are displayed. The cell data is left-aligned, suggesting a string rather than a date. I have tried cast(myvar as date) and convert(varchar, myvar, 101) and convert(varchar, myvar, 1) in the base table, but none of these have been picked up by Excel as dates.

If the column is recognized as a date, I can group by week and month. I understand that if I can't fix this, the next step is to add columns with weeks and months for each date to the table, but I'd like to give formatting the column one more shot before doing that.

Best Answer

Try casting your date to datetime.

SQL Server's date is internally represented as a 3 byte integer whereas datetime is 8 bytes and presumably floating point. Excel uses a double to represent dates/times so SQL Server's datetime format might map across better than date.

Related Question