locked
importing SQL 'date' type into pivot tables does not sort as date but as text RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    When importing data with with the data type of 'date' into Excel pivot tables, the dates do not sort as date but as text. If I changed the data type from 'date' to 'datetype' in SQL, the pivot tables will sort correctly. I need the data in SQL to be of type 'date'. Is it any way to export 'Date' type to Excel as dates and not as text? 
    Wednesday, May 15, 2013 9:19 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    I ran into this importing to PowerPivot too. 

    You can just store in SQL as Date datatype as needed, but in the query, Cast to Datetime on the way into Excel.  

    CAST(YourDateColumn as DATETIME) as YourDateColumn

    Then just format the column in Excel as needed (f.e., "MM/dd/yyyy").  This way, you get the compact storage in SQL, and also take advantage of the native datetime features in Excel.

    Hope that helps.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    • Proposed as answer by Elvis Long Friday, May 17, 2013 7:33 AM
    • Marked as answer by Elvis Long Monday, May 27, 2013 2:59 AM
    Thursday, May 16, 2013 8:05 PM
    Answerer