locked
US/European Date Formats RRS feed

  • Question

  • It really pains me to say this but... the dreaded US/European date format problem has reared its ugly head yet again. I've successfully imported data from a csv file which has a date/time field in it. All of the data is from today, ie March 1st 2013. The date format used in the log file is European, so today is 1/3/2013. If I import the data and don't use Change Type then the dates come out as text in US format, ie 3/1/2013. If I do use Change Type/DateTime then things are better - I see the values come out in the Excel table in numeric format (eg 41334.5383217593) and I can format the values there. BUT if I apply an Excel format there, and import the data to the data model, the values get treated as Text and in the PowerPivot window, when I change the date type to DateTime, the values get converted as if they were US format dates so everything comes out as January 1st 2013! It's only if I don't apply an Excel format in the table and load to the model that the correct type is applied and my dates come out correctly....

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Friday, March 1, 2013 9:15 PM

Answers

  • Chris - I will look into this and get back to you. There are two issues you are mentioning/running into.

    First one is around locales and we need to support more locales than en-us. The runtime is capable of supporting other locales, but I need to confirm what is supported in the Excel preview.

    The second issue you are mentioning is generally related to data types and how they flow between DE, Excel and the data model. We are working on a few fixes in this area.

    Thanks.

    Faisal

    Saturday, March 2, 2013 3:44 PM

All replies