Formats of date and time source data

When you define an Analytics table, the source format (input format) of date, datetime, or time data may be auto-recognized by Analytics. For example, Analytics auto-recognizes dates that use the format YYYYMMDD. If the source format is not auto-recognized, you must manually specify the format.

Source format versus display format

Specifying the format of source datetime data is not the same as specifying how Analytics displays datetime data. The source format controls how Analytics reads datetime data in the source file. There must be a one-to-one correspondence between the source format characters that you specify and the actual format of the source data.

Once source datetime data has been successfully defined in Analytics, you can choose to display it in a variety of different formats. Choosing to display datetime data in different formats does not affect the underlying source format.

For more information about displaying datetime data, see Date and Time options.

Manually specifying the source format

You can manually specify the datetime source format while defining a table in the Data Definition Wizard. Or you can specify the format later in the Table Layout dialog box in Analytics.

With the exception of certain separator characters, the format you specify must exactly match the format of the source data for the source data to appear correctly in Analytics.

Guidelines for specifying separator characters in datetime formats

Source datetime data often includes separator characters:

• Characters such as slashes (/) between the day, month, and year components of dates
• Characters such as colons (:) between the hour, minute, and second components of times
• A space, or a character such as ‘T’, between the date and time portions of datetime values
• A character such as ‘T’ or a decimal point before standalone time values
• For local times with a time zone indicator, a plus (+) or minus (-) sign before the UTC offset

Analytics auto-recognizes some, but not all, of these separator characters in the source data.

Follow the guidelines below when specifying separator characters in datetime formats. Omitting or incorrectly specifying separator characters can prevent datetime data from displaying, or from displaying correctly.

Function of separator character	Specify in format?	For this source data:	Specify this format:
Separates day, month, and year components of dates	Required	31/12/2014	DD/MM/YYYY
Separates hour, minute, and seconds components of times	Optional	23:59:59	hh:mm:ss hhmmss
Separates the date and time portions of datetime values (single space)	Optional	31/12/2014 23:59:59	DD/MM/YYYY  hh:mm:ss DD/MM/YYYYhh:mm:ss DD/MM/YYYY  hhmmss DD/MM/YYYYhhmmss
Separates the date and time portions of datetime values (‘T’ or ‘t’)	Disallowed	31/12/2014T235959	DD/MM/YYYY  hhmmss DD/MM/YYYYhhmmss
Prefaces standalone time values (‘T’ or ‘t’)	Disallowed	T235959	hhmmss
Separates the date and time portions of datetime values that use a Numeric data type (decimal point)	Optional	31122014.235959	DDMMYYYY.hhmmss DDMMYYYYhhmmss
Prefaces standalone time values that use a Numeric data type (decimal point)	Optional	.235959	.hhmmss hhmmss
Prefaces a UTC offset (plus or minus sign)	Required	T235959-0500	hhmmss-hhmm hhmmss+hhmm

Date and time separators

In order for Analytics to read datetime values from source data, the date and time components in the source data must be separated by a space or a separator character. For example:

• 2014/12/31 23:59:59
• 20141231.235959

For datetime values that use a Datetime data type, or a Character data type, Analytics recognizes the following separators:

• <date> <time> (single space)
• <date>T<time> (uppercase ‘T’)
• <date>t<time> (lowercase ‘t’)

For datetime values that use a Numeric data type, Analytics recognizes only the following separator:

• <date>.<time> (decimal point)

Standalone time data

In order for Analytics to read standalone time values from source data – for example, 23:59:59 – the time value in the source data must be prefaced by a space or a separator character, or the time components must be separated by colons. For example:

• 23:59:59
• .235959

For time values that use a Datetime data type, or a Character data type, Analytics recognizes the following separators:

• _<time> (single space)
• T<time> (uppercase ‘T’)
• t<time> (lowercase ‘t’)
• <hh>:<mm>:<ss> (colons)

For time values that use a Numeric data type, Analytics recognizes only the following separator:

• .<time> (decimal point)

Date formats

There are many date formatting conventions in use. In the Data Definition Wizard, and the Table Layout dialog box, you can select from among several common date formats. If necessary, you can modify or create a date format to match the source data.

Date formats apply to date data, or to the date portion of datetime data. Several common date formats are shown below:

Common date format	Type	Example using December 31, 2014
YYYY-MM-DD	ISO	2014-12-31
MM/DD/YYYY	American	12/31/2014
DD/MM/YYYY DD.MM.YYYY DD-MM-YYYY	European	31/12/2014 31.12.2014 31-12-2014
YYYYDDD	Julian (Ordinal)	2014365

Day, month, and year characters

When you specify a date format, you are specifying which components in the source data represent the day, the month, and the year. In Analytics, the format characters shown below are used to represent the day, month, and year components of a date.

If separators such as the slash symbol (/) exist in the source data, you need to insert the same symbol in the same relative position in the date format. Otherwise, Analytics will not interpret the date correctly.

Format characters	Date component
DD	Day (1 – 31)
DDD	Julian day/ordinal day (1 – 366)
MM	Month (1 – 12)
MMM	Month name (Jan – Dec)
YY	Short year format (00 – 99)
YYYY	Long year format (1900 – 9999)

Time formats

Analytics supports the most common time formatting convention – hh:mm:ss – and some minor variations of this format. In the Data Definition Wizard, and the Table Layout dialog box, you can select from among several common time formats. If necessary, you can modify or create a time format to match the source data.

Time formats apply to time data, or to the time portion of datetime data.

Hour, minute, and second characters

When you specify a time format, you are specifying which components in the source data represent the hour, the minutes, and the seconds, and if they are present, the AM/PM indicator, and the UTC offset indicator. In Analytics, the format characters shown below are used to represent the various components of time data.

Format characters	Time component
hh	Hour (00 – 23)
mm	Minute (00 – 59)
ss	Second (00 – 59)
: (colon)	time component separator
A or P	AM/PM indicator (A and P)
AM or PM	AM/PM indicator (AM and PM)
+ or -	UTC offset indicator (+ and -)