Date format

M.Lambert
M.Lambert Contributor
in General

Hi there, I am having trouble with some date data. ADS is not recognizing the dates that comes from Azure Data Lake. Is that anyway to change that?

Answers

  • Josh Boxer
    Josh Boxer Administrator

    Yes. Can you provide some further detail or reach out to your Experian contact who can help with your specific use case.

  • M.Lambert
    M.Lambert Contributor

    Hey Josh, I am trying to get a year from the dates I have but aperture does not recognize as a date field.

    As we deal with data from different sources it comes in different formats, like in the example bellow.

    image.png

  • Josh Boxer
    Josh Boxer Administrator
    edited September 20

    Thanks for sharing the example (would be useful to see the entire column value!), but likely the microseconds causing an issue. If you only want the Year then start by keeping only the values Before the space character then extract the Year:

    image.png

  • Henry Simms
    Henry Simms Administrator
    edited September 20

    This will be down to the format of your datetime string, as not all input formats will be parsed as DATE datatype by Data Studio. For example, when loading these datetime value:

    id

    datetime

    1

    01-02-1970 12:34:56.987

    2

    1970-02-01 12:34:56.987

    3

    1970-02-01 12:34:56.000+01:00

    4

    1970-02-01 12:34:56.000000

    If we allow Data Studio to auto-parse the values, the 4th value (with microseconds rather than milliseconds) is not parsed as a date, and is assigned the datatype ALPHANUMERIC

    image.png

    It would be interesting to see exactly what your datetime format is.

    As per Josh's answer, the value can be transformed and then converted to data in Data Studio (my approach is typically to use the Create Date/timestamp function to create your Date type, which will involve some text manipulation like substring-ing).

    You could also request that this datetime format be supported by the auto-parsing by submitting a new idea to our product team.

  • M.Lambert
    M.Lambert Contributor

    Hey Josh and Henry, thanks for that. The get year it was more to see if ADS was recognizing the datatype as a date but what I really need is to get all different ways in which the date is coming and be able to put it in the same format so when I join the sources and need to filter or sort by date it will not be a problem. I actually tried doing the before function and then formatting as 'yyyy-MM-dd' to be the same but it doesn't work as it does not recognize as a date, so it takes me back to the beginning 😳. I like the idea of submitting a new idea to the product team, surely I'm not the only one struggling with that.

  • Tanj Jagpal
    Tanj Jagpal Administrator
    edited September 24

    @M.Lambert sounds like a great idea, please post it here so we can start collecting votes against it:

    https://community.experianaperture.io/categories/ideas-board

Categories