Fun with Date strings

I have a hunch that others have encountered this and solved it in various ways, so I am sharing my ugly but functional solution. The problem is that I want to use the string representation of a date as an accumulator, but there is no datetime_to_date_string function that can be used within a query body. Also, if you just concatenate numeric year, month and day, it will not work because the month is not padded with leading zeroes, so your sorting will be wrong.

Here is my solution:

ACCUM d.@dates += ( to_string(year(h.actualAdmissionDate)) + “-” + trim(LEADING “2” FROM to_string(20000 + month(h.actualAdmissionDate) * 100 + day(h.actualAdmissionDate))) -> 1);

you end up with nice sortable strings like

2020-0124
2020-0216
2020-0301
etc.

3 Likes