locked
Format data from SQL Server view as date in Excel RRS feed

  • Question

  • Sorry but I didn't know which category would describe my question the best way.
    I get some data from a view in SQL Server to display it in Excel.
    The view returns a column with the type "Date", which is displayed as a normal text in Excel (like "2014-11-31").
    I want to create a Pivot table from this raw data, so it's important that the type of this column is really "Date".

    Do I really have to return the data as a string in the correct format (for example "31.11.2014" here in Germany)?
    I would like to give less experienced people access to the view, so solutions where users don't have to change a lot manually are preferred (even if it's a strange string cast).

    Thanks for your help!

    Wednesday, June 4, 2014 1:04 PM

Answers

  • I've had similar experiences, and in my case, I think the problem was that I was using MDAC from several years ago, when SQL did not have "date" and "time" data types.  When I import data directly into Excel from SQL, the "date" fields come across as text.  However, the middleware did recognize smalldatetime values as dates.  What I've done to get around this problem is to cast the dates as smalldatetime in the query that I import to Excel.  It's a little clumsy, but it did allow date values to come across directly into Excel and be recognized as dates.  Worth a try, anyway.

    Doug Hudson

    Saturday, June 28, 2014 2:16 AM

All replies

  • Sorry but I didn't know which category would describe my question the best way.
    I get some data from a view in SQL Server to display it in Excel.
    The view returns a column with the type "Date", which is displayed as a normal text in Excel (like "2014-11-31").
    I want to create a Pivot table from this raw data, so it's important that the type of this column is really "Date".

    Do I really have to return the data as a string in the correct format (for example "31.11.2014" here in Germany)?
    I would like to give less experienced people access to the view, so solutions where users don't have to change a lot manually are preferred (even if it's a strange string cast).

    Thanks for your help!

    so you are saying that you don't know what is the source data type of the column and you wanted to make sure it is date data type right?


    - please mark correct answers

    Wednesday, June 4, 2014 6:00 PM
  • Hi Jeff,

    If PowerPivot treated the date column as text, you can try to directly convert text data type into date in the PowerPivot data model. For detail information, please see:
    Converting Text to Dates in PowerPivot: http://www.microsofttrends.com/2013/12/30/converting-text-to-dates-in-powerpivot/

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, June 5, 2014 10:18 AM
  • Thanks for your answers!

    My problem seems to be, that Excel doesn't recognize the data I import as a date.
    I'm using the the Excel connection tool to connect to SQL Server and get the data from my view.
    Even if I change the formatting of the column in Excel to Date nothing happens.

    I can "solve" the problem by double clicking in each cell, the formatting gets updated then and I have a valid date which can be used for grouping in a pivot table or similar.
    But of course I can't tell the users they have to click 30k cells each time they refresh the data.

    It seems to be the same problem as here, but they don't have a solution as well:
    http://superuser.com/questions/459053/importing-specific-date-format-to-excel-from-microsoft-sql-server

    Any hints are greatly appreciated!

    Thursday, June 5, 2014 4:35 PM
  • Jeff,

    Any progress with this issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, June 27, 2014 11:39 PM
  • I've had similar experiences, and in my case, I think the problem was that I was using MDAC from several years ago, when SQL did not have "date" and "time" data types.  When I import data directly into Excel from SQL, the "date" fields come across as text.  However, the middleware did recognize smalldatetime values as dates.  What I've done to get around this problem is to cast the dates as smalldatetime in the query that I import to Excel.  It's a little clumsy, but it did allow date values to come across directly into Excel and be recognized as dates.  Worth a try, anyway.

    Doug Hudson

    Saturday, June 28, 2014 2:16 AM
  • Thank you Doug, this is a solution.
    A simple CAST(MyDateColumn AS smalldatetime) did the trick.

    Monday, June 30, 2014 4:03 PM
  • I found this worked perfectly. It also took a fake SQL date stored as a varchar and converted to a date. Nice and simple.
    Friday, December 22, 2017 10:54 AM