Text to Date conversion doesn't handle 2-digit year correctly

Feedback
#1

When changing a field type from Text to Date, where the records contain strings like “1/1/22”. The resulting records contain dates like “01/01/0022”. Converting to ISO date has especially weird results: “1/1/22” becomes “0001-01-22”, but “1/1/99” becomes “1999-01-01”.

I did find a workaround, in case anyone else is looking for a solution to this. Create a formula field with the formula: todate(field("Text Field"), "MM/DD/YY") (or “DD/MM/YY” if you prefer), then change the type of that field to Date. The todate function seems to get the two digit year right every time.

#2

Hello @Jamac! The key here is to stick to one date format in the Text field type and Date field type — in that case, conversion works perfectly.

Another workaround: if you have the European date format in the Text field type, and you want to convert it to the ICO date format but in the Date field type, try to convert the Text field type to the Date field type but keep the European date format as the first step, and then just switch from European to ICO date format. I’ve just tested and it worked for me :ok_hand:

#3

You’re right, keeping the date in the proper format before converting would solve this issue, but the point I want to make is that it’s not intuitive. Any human can look at the text “1/1/22” and know that it means the date “1/1/2022” - so why can’t Baserow?

Also, the reason I ran into this was actually because I was importing data from a CSV exported by another software, which exports dates with 2-digit years.

#4

Got you! I added this topic to the list of discussions, and will get back to you as soon as there are any updates on this topic :raised_hands: