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.
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
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.