Issue with dates being read as American dates

beth.kirbybeth.kirby Learner
edited January 2022 in General discussion

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?


  • beth.kirbybeth.kirby Learner
    edited January 2022

  • Josh BoxerJosh 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.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 RaoPavan 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.

  • Josh BoxerJosh Boxer Administrator

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

  • Henry SimmsHenry Simms Experian Super Contributor

    Here's an article from a while ago that covers similar things to the answers already given:

Sign In or Register to comment.