Eloqua stores dates and times internally in a specific date/time data type in the database. However, when contact lists are uploaded, the dates and times are text, and need to be interpreted by the system.

 

In E10's Contacts > Contacts > Upload or Contacts > Segments > Upload Contacts wizards, there is no selection for date format (unlike other parts, where there's a DD/MM/YYYY vs MM/DD/YYYY selector). There are many formats that the system will accept, but my personal favourite is:

 

       YYYY-MM-DD HH:mm:ss (e.g. 2014-10-15 15:21:14)

Unlike the other two common date formats, alphabetical order is automatically chronological order, and this format is much less ambiguous. I hope many of you will join me in the war against ambiguity.

 

     MM/DD/YYYY hh:mm:ss A/PM (e.g. 10/15/2014 3:15:14 AM)

This format is also acceptable for the purposes of the list upload wizards mentioned above.

 

It's also worth noting that the system can accept thousandths of a second (three decimal places), but it doesn't look like that extra data is stored.

 

    "2014-10-15 15:21:14.254" is acceptable. "2014-10-15 15:21:14.2545" will fail.

 

The following table shows different test text data, and the resulting interpreted/recorded date/time:

Date-Only Formats
Date/Time Text (in CSV)Recorded Date/Time (GMT-5 / 24 hr)Note
10/13/20142014-Oct-13 00:00:00MM/DD/YYYY
10/13/142014-Oct-13 00:00:00MM/DD/YY
01/02/032003-Jan-02 00:00:00MM/DD/YY
2014/10/132014-Oct-13 00:00:00YYYY/MM/DD
2014.10.132014-Oct-13 00:00:00YYYY/MM/DD
2014-10-132014-Oct-13 00:00:00YYYY/MM/DD
14/13/10INVALIDMonth '14' does not exist (MM/DD/YY)
13/10/2014INVALIDMonth '13' does not exist (MM/DD/YYYY)
13/10/14INVALIDMonth '13' does not exist (MM/DD/YY)
2014/13/10INVALIDMonth '13' does not exist (YYYY/MM/DD)
2014-13-10INVALIDMonth '13' does not exist (YYYY/MM/DD)
3015-10-133015-Oct-13 00:00:00Years are saved in DB as four-digits
10/11/30153015-Oct-11 00:00:00Years are saved in DB as four-digits
30105-10-13INVALIDFive-digit years are not supported
10/13/30105INVALIDFive-digit years are not supported
Oct 18INVALIDMonths must be numeric
18 OctINVALIDMonths must be numeric
OctINVALIDMonths must be numeric
13/10INVALIDCannot have only YY/MM
2014/10INVALIDCannot have only YYYY/MM
10/13INVALIDCannot have only MM/YY
10/2014INVALIDCannot have only MM/YYYY
01/02INVALIDCannot have only MM/DD (or DD/MM)
10.11.122012-Oct-11 00:00:00. is used as a date separator (MM/DD/YY)
10:11:121900-Jan-01 10:11:12: is used as a time separator (HH/mm/ss)
Date/Time Formats
Date/Time Text (in CSV)Recorded Date/Time (GMT-5 / 24 hr)Note
10/13/2014 14:30:15.2522014-Oct-13 14:30:15MM/DD/YYYY HH:mm:ss.fff
2014/10/13 2:30:15.5642014-Oct-13 02:30:15YYYY/MM/DD H:mm:ss.fff
2014/10/13 2:30:15.564 PM2014-Oct-13 14:30:15YYYY/MM/DD H:mm:ss.fff xM
2014.10.13 14.30INVALIDTime separator should be : (except for fractions of a second)
2014/10/13 2:30:15.5642 PMINVALIDFractions of a second cannot have more than three digits
10/13/2014 14:30:15.252 AMINVALIDCannot specify AM when HH > 12
10/12/2014 2:00PINVALIDCannot use A or P (AM or PM must be used)
2014/10/13 2:30:15.564 AINVALIDCannot use A or P (AM or PM must be used)
2014/10/13 2:30:15.564 PINVALIDCannot use A or P (AM or PM must be used)
2014-10-13 17.25.14.147INVALIDTime separator should be : (except for fractions of a second)
10-11-12 2-5-14INVALIDTime separator should be : (except for fractions of a second)
10-11-12 2.5.14INVALIDTime separator should be : (except for fractions of a second)
10/13/2014 14 PM2014-Oct-13 14:00:00Minutes/Seconds not required when AM/PM is specified
10/13/2014 14 AMINVALIDCannot specify AM when HH > 12
10/13/2014 2 PM2014-Oct-13 14:00:00Minutes/Seconds not required when AM/PM is specified
10/13/2014 14INVALIDTime requires either Minutes or AM/PM
10/12/2014 2:00PM2014-Oct-12 14:00:00Space is not required between ss and AM/PM
2014/10/12 2:00PM2014-Oct-12 14:00:00Space is not required between ss and AM/PM
10/12/2014-2:00PMINVALIDMust use a space between the date and the time
2014/10 2:00 PMINVALIDCannot have only 2 of 3 date components
10/13 2:00INVALIDCannot have only 2 of 3 date components
13/10 2:00INVALIDCannot have only 2 of 3 date components
2014 2:00 PM2014-Jan-01 14:00:00Year only + time is okay
Time-Only Formats
Date/Time Text (in CSV)Recorded Date/Time (GMT-5 / 24 hr)Note
2:45:10 AM1900-Jan-01 02:45:10Date defaults to 1900-Jan-01 when only time is specified
14:12:251900-Jan-01 14:12:25Date defaults to 1900-Jan-01 when only time is specified
14:001900-Jan-01 14:00:00Date defaults to 1900-Jan-01 when only time is specified
2 PM1900-Jan-01 14:00:00Minutes/Seconds not required when AM/PM is specified
2 PINVALIDCannot use A or P (AM or PM must be used)
14INVALIDTime requires either Minutes or AM/PM
10.11.122012-Oct-11 00:00:00. (period) is used as a date separator (MM/DD/YY)
10:11:121900-Jan-01 10:11:12: (colon) is used as a time separator (HH/mm/ss)
24.15.14INVALIDMM cannot be > 12
24:15:14INVALIDHH cannot be > 23
10 11 12INVALID<space> cannot be used as a separator within a date or time (it must be used between the date and the time)
10 11INVALID<space> cannot be used as a separator within a date or time (it must be used between the date and the time)
10/12/2014-2:00PMINVALIDMust use a space between the date and the time

 

 

For details on the effects of time zones, see Time Zones Across Eloqua

 

 

Attached is a spreadsheet of different date/time tests performed, and how they were interpreted by the system (source data for above table).