When you define an Analytics table,the source format (input format) of date, datetime, or time datamay be auto-recognized by Analytics. For example, Analytics auto-recognizesdates that use the format YYYYMMDD. If the source format is notauto-recognized, you must manually specify the format.
Source format versus display format
Specifying the format of source datetime data is not the sameas specifying how Analytics displays datetime data. The sourceformat controls how Analytics reads datetime data inthe source file. There must be a one-to-one correspondence betweenthe source format characters that you specify and the actual format ofthe 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 datain different formats does not affect the underlying source format.
For more information about displaying datetime data, see Date and Time tab (Options dialog box).
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 TableLayout dialog box in Analytics.
With the exceptionof certain separator characters, the format you specify must exactly matchthe format of the source data for the source data to appear correctlyin Analytics.
Guidelinesfor specifying separator characters in datetime formats
Sourcedatetime data often includes separator characters:
- Characterssuch as slashes (/) between the day, month, and year components of dates
- Characterssuch as colons (:) between the hour, minute, and second components of times
- A space, or a character such as ‘T’, between the date andtime portions of datetime values
- A character such as ‘T’ or a decimal point before standalonetime values
- For local times with a time zone indicator, a plus (+) orminus (-) sign before the UTC offset
Analytics auto-recognizessome, but not all, of these separator characters in the source data.
Followthe guidelines below when specifying separator characters in datetime formats.Omitting or incorrectly specifying separator characters can preventdatetime data from displaying, or from displaying correctly.
Note
Specifying particularseparator characters in the datetime format can be required, optional,or disallowed, depending on the function of the character.
| Function of separator character | Specify in format? | For this source data: | Specify this format: |
|---|---|---|---|
| Separates day, month, and year componentsof dates | Required | 31/12/2014 | DD/MM/YYYY |
| Separates hour, minute, and seconds componentsof times | Optional | 23:59:59 | hh:mm:ss hhmmss |
| Separates the date and time portions ofdatetime values (single space) | Optional | 31/12/201423:59:59 | DD/MM/YYYYhh:mm:ss DD/MM/YYYYhh:mm:ss DD/MM/YYYYhhmmss DD/MM/YYYYhhmmss |
| Separates the date and time portions ofdatetime values (‘T’ or ‘t’) | Disallowed | 31/12/2014T235959 | DD/MM/YYYYhhmmss DD/MM/YYYYhhmmss |
| Prefaces standalone time values (‘T’or ‘t’) | Disallowed | T235959 | hhmmss |
| Separates the date and time portions ofdatetime values that use a Numeric data type (decimal point) | Optional | 31122014.235959 | DDMMYYYY.hhmmss DDMMYYYYhhmmss |
| Prefaces standalone time values that usea Numeric data type (decimal point) | Optional | .235959 | .hhmmss hhmmss |
| Prefaces a UTC offset (plus or minussign) | Required | T235959-0500 | hhmmss-hhmm hhmmss+hhmm |
Dateand time separators
In order for Analytics to read datetimevalues from source data, the date and time components in the sourcedata must be separated by a space or a separator character. Forexample:
- 2014/12/3123:59:59
- 20141231.235959
For datetime values thatuse a Datetime data type, or a Character data type, Analytics recognizes thefollowing separators:
- <date> <time>(single space)
- <date>T<time>(uppercase ‘T’)
- <date>t<time>(lowercase ‘t’)
For datetime values that use a Numericdata type, Analytics recognizes only the following separator:
- <date>.<time>(decimal point)
Note
Analytics can read datetimevalues that use a Datetime or Character data type and have a periodas a separator – <date>.<time>.However, the period separator is not officially supported becausein some situations results can be unreliable.
Standalonetime data
In order for Analytics to read standalone timevalues from source data – for example, 23:59:59 – the time valuein 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 Datetimedata type, or a Character data type, Analytics recognizes the followingseparators:
- _<time> (singlespace)
- T<time> (uppercase ‘T’)
- t<time> (lowercase ‘t’)
- <hh>:<mm>:<ss>(colons)
For time values that use a Numeric datatype, Analytics recognizes only the following separator:
- .<time>(decimal point)
Note
Analytics can read time valuesthat use a Datetime or Character data type and have a period asa separator – .<time>. However, the periodseparator is not officially supported because in some situationsresults can be unreliable.
Dateformats
There are many date formatting conventions inuse. In the Data Definition Wizard, and the TableLayout dialog box, you can select from among severalcommon date formats. If necessary, you can modify or create a date formatto match the source data.
Date formats apply to date data, orto the date portion of datetime data. Several common date formatsare 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 |
| YYDDD | Julian | 14365 |
Day, month, and year characters
When you specify a date format, you are specifyingwhich components in the source data represent the day, the month,and the year. In Analytics, the format characters shown below areused to represent the day, month, and year components of a date.
Note
These characters are the default, and they can be changed in the Options dialogbox.
If separators such as the slash symbol (/) existin the source data, you need to insert the same symbol in the samerelative position in the date format. Otherwise, Analytics willnot interpret the date correctly.
| Format characters | Date component |
|---|---|
| DD | Day (1 – 31) |
| DDD | Julian day (1 – 366) |
| MM | Month (1 – 12) |
| MMM | Month name (Jan – Dec) |
| YY | Short year format (00 – 99) |
| YYYY | Long year format (1900 – 9999) |
Examples ofspecifying the date format for source data
| Analytics date format | Source data |
|---|---|
| YYYY-MM-DD | 2014-12-31 |
| YYYYMMDD | 20141231 |
| MM/DD/YYYY | 12/31/2014 |
| MM/DD/YY | 12/31/14 |
| DD/MM/YYYY | 31/12/2014 |
| YYDDD | 14365 |
| MMM DD, YYYY | Dec 31, 2014 |
| DD MMM YYYY | 31 Dec 2014 |
Timeformats
Analytics supports the most common time formattingconvention – hh:mm:ss – and some minor variations of thisformat. In the Data Definition Wizard, andthe Table Layout dialog box, you can selectfrom among several common time formats. If necessary, you can modify orcreate 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
Whenyou specify a time format, you are specifying which components inthe source data represent the hour, the minutes, and the seconds,and if they are present, the AM/PM indicator, and the UTC offsetindicator. In Analytics, the format characters shown below are usedto represent the various components of time data.
Note
The hour, minute, and second characters shown below are the default, and they can be changed in the Options dialogbox.
| 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 -) |
Examples ofspecifying the time format for source data
| Analytics time format | Source data |
|---|---|
| hh:mm | 23:59 |
| hh:mm A | 11:59 P |
| hhmm PM | 1159 PM |
| hh:mm:ss | 23:59:59 |
| hh:mm:ss P | 11:59:59 P |
| hhmmss AM | 115959 PM |
| hh:mm:ss+hh:mm | 23:59:59-05:00 |
