Hello. Today I came onto an issue relating to dates.
I'm loading a dataverse table from an API. The date from the API comes as a string, like "yyyyMMdd", e.g "20221125".
I then slice that string 3 times and concatenate it to have the string "25/11/2022", and then use parseDateTime function to convert it to "date" data-type.
However, when that data is loaded onto the table I get an odd result as shown here:
As you can see, the date value shown in the table is offset by 1 day before. Instead of the correct "01/01/2021", I get "31/12/2020" shown, and a complete different format in the actual value. That happens to every date in every registry I load. So for example, "25/11/2022" is shown as "24/11/2022" and it's actual value is 2022-11-25T00:00:00Z".
This is undesirable as I have to filter some information within a date range and the results are all wrong.
This is information from the loaded table, they all should be "01/01/2021".
This is the expression used to parse the date. I think the error is here. I don't get anything else related to dates from the API other than that string.
Can someone help me figure it out? The steps I want are:
1. Get string in yyyyMMdd, e.g, "20221125",
2. Transform it to string "25/11/2022", and then parse that string into "date" format
3. Load it into an "date-only (no time)" data-type column. I don't care about time, just dd, MM and yyyy.
Up until step 2, it's done successfully. Input is the string, then expression converts it to the desired format:
So error must be on the parseDateTime previously mentioned or some other behavior I'm not aware of.
Thank you in advance.
