Date fields are used in almost every type of import. For example, KPI value imports require a date field, initiative imports have start dates, and datasets can have date or time fields. If your date fields are coming from a single field in a database or Excel spreadsheet, it's easy for Spider Impact to know how to import the date.
When your dates are coming from a text format like CSV or JSON, however, or when they're the result of combining multiple fields, things can get a little more tricky. That's because different countries and languages store dates differently, and Spider Impact doesn't have any clues from the source file to determine how to read the dates. In these situations, the software goes through a specific order of possible date formats, many of which are unique to each locale.
Don't worry, you probably won't have to even think about this. If you try to import dates that look good to you, there's a good chance Spider Impact will be able to import them. For the sake of transparency, however, here are the full details of how Spider Impact imports dates.
Attempt 1: Bulgaria
If the user's browser locale is set to Bulgaria, Spider Impact will first attempt to parse text like 01.25.2021 as January 14th, 2022. If the user isn't from Bulgaria or the text isn't in that specific format, the software will continue to attempt 2.
Attempt 2: Browser locale-based formats
For most users, the first thing that Spider Impact tries when trying to turn text into a date is Java's built-in date parsing. It looks at the user's browser to determine their locale, and then asks Java to treat the text as a date for that locale.
There are four formats for each locale: Full, Long, Medium, and Short. For example, here is the date January 14th, 2022 for each of the formats for the United States locale.
|Full||Friday, January 14, 2022 AD||3:46:00pm EST||Friday, January 15, 2022 at 1:12:45 PM Eastern Standard Time|
|Long||January 14, 2022||3:46:00 PM EST||January 14, 2022 at 1:46:00 PM EST|
|Medium||Jan 14, 2022||3:46:00 PM||Jan 14, 2022, 15:46:00 PM|
|Short||01/14/22||3:46 PM||1/14/22, 3:46 PM|
Java supports over 4,000 locales, so we've put together a spreadsheet with examples for every locale.
Attempt 3: Specific formats
If none of the locale-based formats match the text, Spider Impact will then attempt to parse the text using this specific list of formats. We're continuing to use January 14th, 2022 as an example. Missing year is assumed to be current year.
jan 14, 2022
Jan 14, 2022
JAN 14, 2022
jan 14 2022
Jan 14 2022
JAN 14 2022
January 14, 2022
january 14, 2022
January 14 2022
january 14 2022
14 January 2022
14 january 2022
To create a Datetime field, Spider Impact combines any of the date formats with any of the time formats. The date and time is separated by either a [space] or T.
Attempt 4: No day of the month formats
If the date doesn't match any of the formats specified above, Spider Impact tries the following formats, assuming that it is the first day of the month. We're using January 2022 as an example.
Attempt 5: ISO 8601 date format
If none of the date formats match, Spider Impact will attempt to parse the dates as ISO 8601. This is a well-defined format, but here are some examples for January 14th, 2022.