Form - Cleaning Date Time data
From All n One's bxp software Wixi
Date and time formats vary wildly. American and European dates can become easily confused when used in Excel or other tools which think they're being used on a different continent.
Throughout bxp date time fields use Universal Date Time format. This means YYYY-MM-DD HH:mm:SS
- Y : Year
- M : Month
- D : Day
- H : Hour
- m : minute
- S : Second
Universal Date Time format has a number of distinct advantages.
- It's unambiguous.
- When ordered by a database and the contents are a string (as opposed to a date time field), they will order correctly
To explain this last point. You have two dates 29/01/2017, which is the 29th of January 2017. You also have the 01/09/2017, the 1st of September 2017. In an ordered list you would expect this to be
Unfortunately by default when this data is text, 0 is < 2 so the list actually outputs as
With UDT, even if the data is text and not a date time field, it will always sort correctly.
2 Cleaning data
When you upload data into bxp from an Excel spreadsheet or even injected from an external website or app, the data can come in, in European or American format. To save time, bxp has a mass date time correction function.
Main Menu > Form - Management > Form - Data Cleaning > Mass Update - Convert data to UDTF