Issue with dates being read as American dates

beth.kirby
beth.kirby Member
edited December 2023 in General

I am pulling in my source data with a column that contains dates of the format dd/mm/yyyy. When annotating the columns, I have specified the column to be a date column. Now ready to explore my data, most of the dates in the column have correctly loaded in format dd/mm/yyyy. However some have converted to format dd-MMM-yyyy and infact it has switched the day and the month before changing format. So for example in my source data I have the date 03/11/2020 is appearing as 11-Mar-2020 in Aperture. I believe this is happening because Aperture is reading the date in American date format (and so switching the days and months for those where the day is between 1-12 and those corresponds to a correct date). I have checked and my internet browser language is in english UK. How can I stop my dates being converted to American Date format?

Tagged:

Answers

  • beth.kirby
    beth.kirby Member
    edited January 2022


  • Josh Boxer
    Josh Boxer Administrator

    Hi Beth

    It sounds like you are correct, Data Studio is reading dates in American date format. Most dates (those with day greater than 12) are actually being parsed as strings since they are invalid (American) dates.

    The locale gets picked up from the o/s (not your browser) so I would assume that is set incorrectly. Ideally you or your IT team can update Windows?? to match your current locale, but if this is not possible then it can be overwritten in the configuration settings file named "Aperture Data Studio Service 64bit.ini", by adding " -Duser.country=GB -Duser.language=en" to the "Virtual Machine Parameters="


    An interim solution for right now could be to use the Function 'Create date/time stamp' (usually used where lists of dates have mixed formats). Along with the Function 'Substring' to extract characters 1-2 as day, then again with 4-5 as month, etc.

  • Pavan Rao
    Pavan Rao Experian Contributor
    edited January 2022

    Hi Beth, while uploading the file, we have the option to choose the Language and Region to "English (United Kingdom)" which will help fix the date format as it might have picked "English (United States)" to begin with. Let me know if that resolves this format issue.

    image.png


  • Josh Boxer
    Josh Boxer Administrator

    Thanks Pavan. That "Language and region" setting is automatically set by the Server locale as detailed above.

  • Henry Simms
    Henry Simms Administrator

    Here's an article from a while ago that covers similar things to the answers already given: https://community.experianaperture.io/discussion/596/changing-data-studios-default-language-and-region-setting

Categories