parseDateTime converts 12/1/22 year to 12/1/0022

I’m using the excel importer. There are multiple decimal fields and one column “TicketDate” is a date time, but I’m storing all the values as strings, because instead of failing with an ugly message if there’s a problem, I want all the correct rows to import and I’m doing some custom parsing with error handling to let the user know which field there was a failure. I mimic my “Ticket” entity with a “TicketImport” entity, making all fields strings. Loop through each TicketImport, parse the fields in a custom way and then create a Ticket.   In excel I have the date field for some rows as ‘12/1/2022’ (I’m American, I know… we put the month first). Keep in mind the object is the TicketImport entity where everything is a string. When I debug, I can see that the imported value was automatically changed to ‘12/1/22’. It seems to AUTOMATICALLY convert the year to 2 digits, even though it’s just a string.    I do an error check to make sure it’s a date. If it fails, I alert the user. If it passes, I use the following expression to store into the ACTUAL TicketDate field of the REAL entity:   parseDateTime($IteratorTicketImport/TicketDate,'MM/dd/yyyy')   The date that gets saved? It’s “12/1/0022”   First off, I’m not sure why its converting the ‘12/2/22’ to ‘12/1/22’ on the import. I assume it’s someting weird with Excel. BUT… You would THINK that the parseDateTime function in mendix would save it as 2022 properly. It isn’t. It’s saving the year as “0022”. Does anyone know why? I’m trying to have to avoid string parsing and concatenating “20” to the front of the year.
1 answers

Excel can do some weird date format stuff. In excel you can specifically set the date format for the date column using the formatting options.


If the excel importer is reading 12/1/22 from excel than this should be the format that you use in the parse date function, for example: parseDateTime($IteratorTicketImport/TicketDate,'MM/dd/yy')

The Java docs on date formatting explains how to handle 2 digit year.

SimpleDateFormat (Java SE 11 & JDK 11 ) (