how to sort a sequence of numbers treating them as a date but without zeros

Question

These are sample 3 strings. The numerical sequence has no zeros. The correct date is on the right

P_2024713812 - "2024-07-13 08:12"

P_20241151212 - "2024-11-05 12:12" <-- last

P_202411111 - "2024-01-01 11:01"

How to get the last one?

Answer 1

You cannot sort it as some results are ambiguous.

For example:

202411111 could be:

• 2024-11-01 01:01 or
• 2024-01-11 01:01 or
• 2024-01-01 11:01 or
• 2024-01-01 01:11

20241151212 could be:

• 2024-01-15 12:12 or
• 2024-11-05 12:12

2024713812 is the only non-ambiguous value and must be:

• 2024-07-13 08:12

You have no way of knowing which answer is the correct one and there is approximately 11 months difference between the minimum and maximum possible value for 202411111 which means that the other rows could be before or after it depending on how the ambiguous formatting is interpreted.

These are all valid orderings:

• 202411111, 20241151212, 2024713812

  as 2024-01-01 01:11, 2024-01-15 12:12, 2024-07-13 08:12

• 202411111, 2024713812, 20241151212

  as 2024-01-01 01:11, 2024-07-13 08:12, 2024-11-05 12:12

• 20241151212, 2024713812, 202411111

  as 2024-01-15 12:12, 2024-07-13 08:12, 2024-11-01 01:01

• 2024713812, 202411111, 20241151212

  as 2024-07-13 08:12, 2024-11-01 01:01, 2024-11-05 12:12

Each of the values could be ordered first, middle or last in different combinations of interpretations.