Overview

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.

Details

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.

Java supports over 4,000 locales, so we've put together a spreadsheet with examples for every locale.
http://resources.spiderstrategies.com.s3.amazonaws.com/docs/date_time_by_locale.csv

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.

Dates

1/14/22
01/14/22
1/14/2022
01/14/2022
1/14
01/14
1-14-22
01-14-22
1-14-2022
01-14-2022
2022-1-14
2022-01-14
1-14
01-14
jan 14, 2022
Jan 14, 2022
JAN 14, 2022
jan 14 2022
Jan 14 2022
JAN 14 2022
MMM-d-yyyy
jan-14-2022
Jan-14-2022
JAN-14-2022
January 14, 2022
january 14, 2022
January 14 2022
january 14 2022
jan 14
Jan 14
JAN 14
January 14
january 14
14 January 2022
14 january 2022
14-jan-2022
14-Jan-2022
14-JAN-2022

Times

3:46 pm
3:46 PM
3:46:00 pm
3:46:00 PM
03:46 pm
03:46 PM
03:46:00 pm
03:46:00 PM
15:46
15:46:00
15:46:00.0
15:46:00.00
15:46:00.000
15:46:00.0000
15:46:00.00000
15:46:00.000000

Datetimes

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.

1-14-2022T15:46:00
1-14-2022 15:46:00

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. 

1-2021
01-2021
1/2021
01/2021
2021-1
2021-01
2021/1
2021/01
Jan 2021

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.

2022-01-14
2022-01-14T15:46:00+00:00
2022-01-14T15:46:00Z
20220114T154600Z