Formats of date and time source data

When you define an ACL table, the source format (input format) of date, datetime, or time data may be auto-recognized by ACL. For example, ACL auto-recognizes dates that use the format YYYYMMDD. If the source format is not auto-recognized, you must manually specify the format. You can manually specify the format while defining the table in the Data Definition Wizard, or you can specify the format later in the Table Layout dialog box in ACL. 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 ACL.

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

Once source datetime data has been successfully defined in ACL, you can choose to display it in a variety of different formats. For more information about displaying datetime data, see Options dialog box: Date and Time tab. Choosing to display datetime data in different formats does not affect the underlying source format.

The sections that follow provide additional information about specifying the format of source datetime data:

Guidelines for specifying separator characters in datetime formats

Source datetime data often includes separator characters:

ACL auto-recognizes some, but not all, of these separator characters in the source data. If the source data contains separator characters, specifying particular separator characters in the datetime format can be required, optional, or disallowed, depending on the function of the character. 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 ACL 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:

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

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

Note

ACL can read datetime values that use a Datetime or Character data type and have a period as a separator – <date>.<time>. However, the period separator is not officially supported because in some situations results can be unreliable.

Standalone time data

In order for ACL 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:

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

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

Note

ACL can read time values that use a Datetime or Character data type and have a period as a separator – .<time>. However, the period separator is not officially supported because in some situations results can be unreliable.

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, or if necessary modify or create a date format to match the source data. Date formats can apply to date data, or to the date portion of datetime data. Several common date formats are shown in the table 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

When you specify a date format, you are specifying which components in the source data represent the day, the month, and the year. In ACL, the following format characters are used to represent the day, month, and year components of a date (assuming you have not changed the default Day, Month, and Year format characters in the Options dialog box):

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)

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, ACL will not interpret the date correctly.

The table below provides several examples of specifying the date format for source data.

ACL 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

Time formats

ACL 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, or if necessary modify or create a time format to match the source data. Time formats can apply to time data, or to the time portion of datetime data.

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 ACL, the following format characters are used to represent the various components of time data (assuming you have not changed the default Hour, Minute, and Second format characters in the Options dialog box):

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 -)

The table below provides several examples of specifying the time format for source data.

ACL 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

Related reference
Options dialog box: Date and Time tab


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback