Dates not recognized as dates in pivot table pulling directly from SQL 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.

Michael K

Posted 2011-02-03T16:52:07.240

Reputation: 31

Understand that an Excel date is implemented as a floating-point number whose integer portion is the number of days since some epoch and the fraction is the fraction of 24 hours represented by the time. I'm thinking there's some sort of "interpret date" function so you could read the string date into a hidden cell and then apply the function to it to get an Excel date (but I've never tried that). – Daniel R Hicks – 2014-11-28T13:32:56.240

I should also note that I tried using Text-To-Columns, but I get the error that I can't move parts of the Pivot Table inside the pivot report. – Michael K – 2011-02-03T19:21:21.927

Changing the various cast methods does alter the string that excel shows in the pivot but does not change whether or not excel sees that cell as a date. – Michael K – 2011-02-03T19:48:56.937

Answers

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.

Mike Fitzpatrick

Posted 2011-02-03T16:52:07.240

Reputation: 15 062

1Thanks-- I tried this and also casting as an int - 2, but wasn't able to get excel to recognize the dates. I seem to have identified the problem: this pivot has dates on rows, and thus the content are seen as row labels (strings) rather than values (more interesting data types). In fact, it seems like any of the various castings will be picked up by excel as long as the dates are not on row or column labels. – Michael K – 2011-02-04T14:11:09.973

1This worked for me in Excel 2010 querying SQL Server 2008. After making this change, the values came in as the numeric Excel serialized date and I was then able to format / filter as date. – N Jones – 2013-09-24T15:31:33.783

Try using SQL code as the source for your pivot table, as opposed to the table or query located in SQL Server. Maybe your Cast() statements will be more effective that way.

Click on the pivot table
Click PivotTable tools>Options>Change Data Source(arrow)>Connection Properties
Click Definition
Change Commang Type to 'SQL Query'
Paste or type your SQL statement into the command text area.

I'm assuming you're using Excel 2007. Excel 2003 would be a similar approach, just different way of getting to it.

Sux2Lose

Posted 2011-02-03T16:52:07.240

Reputation: 2 962

This is a fantastic suggestion but we're actually trying to avoid Excel-side execution of the SQL because the procedure is massive. – Michael K – 2011-02-03T19:18:07.580

Use the Convert function with the 120 style, this is the ISO date format, Excel should recognise this.

Hydaral

Posted 2011-02-03T16:52:07.240

Reputation: 1 674