On a system where both server and client have the same Regional Settings applied which display a DateTime as 'dd-MM-yyyy HH:mm:ss' (eg 10-02-2023 11:17:00), we see some strange behaviour when generating dynamic booking shapes which include the start and end time of the booking.
More specifically, we see that a few shapes put the day before the month (which is expected) while most of the other shapes do the opposite, even though they use identical shape data. Please see the highlighted parts in the image below:
Follwing shape data is used within the booking grouped shape:
For the start time:
[Sum:[Subtract:[LocalTime],[UTCTime]],[Start]]
For the end time:
[Sum:[Subtract:[LocalTime],[UTCTime]],[End]]
I already tried to force the format using the '|Format=dd-MM-yyyy HH:mm:ss', but that doesn't seem to work.
Note that the 'Start' and 'End' custom booking properties which are referenced in the shape data, are stored like this:
So, there seem to be a lot of variables at play, but perhaps someone can shed some light, or come up with a solution?
Thanks!
Hi Ruben,
"a system where both server and client have the same Regional Settings applied "
Can you verify that is not overridden in client settings of Cube?
Or, perhaps this should be overridden?
I also struggles sometimes with datetime formats when using Sum or Subtract.
Therefore, I also configure a format specifically:
See documentation as well
Do not forget to use [sep::^] because the split between sessionkey and sessionvalue is using : as split character, while your format also uses character :
Could you try the following?
For the start time:
[sep::@][Sum@[sep::@][Subtract@[LocalTime@format=dd-MM-yyyy HH:mm:ss],[UTCTime@format=dd-MM-yyyy HH:mm:ss]],[Start@format=dd-MM-yyyy HH:mm:ss]|Format=dd-MM-yyyy HH:mm:ss]
For the end time:
[sep::@][Sum@[sep::@][Subtract@[LocalTime@format=dd-MM-yyyy HH:mm:ss],[UTCTime@format=dd-MM-yyyy HH:mm:ss]],[End@format=dd-MM-yyyy HH:mm:ss]|Format=dd-MM-yyyy HH:mm:ss]
Tried a few things together with Matthias, but haven’t come to a solution yet. We’ll be looking into a bit of a different approach now.
Thanks for the support!
The format is sometimes different when the number of the day is higher than 12. When it's 12 or below it can be seen as the month.
Placeholders in Visio are passed along using string representations which sometimes need to be parsed back to types.
I suggest to use the ISO 8601 format as that introduces no ambiguity on what is a day and what is a month. See "o" and "s" format: docs.
Thanks, I’ve already tried both, but no luck.
I assume ‘Format=dd-MM-yyyy HH:mm:ss’ is valid to use within shape data?