Options dialog box: Date and Time tab

You can use the Date and Time tab to specify how dates, datetimes, and times are displayed in ACL, and to configure several other options associated with dates and times.

Date Settings

Day, Month, Year

Use the Day, Month, and Year text boxes to specify the characters that represent these components of date and datetime formats. The default values are ‘D’ for Day, ‘M’ for Month, and ‘Y’ for Year, but you can specify different characters for languages other than English. The characters you specify must be uppercase, they must all be different, and ‘D’, ‘M’, and ‘Y’ can only be used in their default positions.

Date Display Format

This option lets you specify how ACL displays dates, and the date portion of datetimes, in views, reports, and exported files. It also controls the format of log entry dates. You can select one of the formats in the Date Display Format drop-down list, or you can create your own date display format.

Note

This option has no effect on how ACL reads dates from data sources. To specify how ACL reads dates, use the Data Definition Wizard, or the Format field in the Table Layout dialog box. For more information, see Formats of date and time source data.

The format options in the Date Display Format drop-down list are shown in Table 1.

Table 1. Formats in the Date Display Format drop-down list

Select This Display Format

To Display This

DD/MM/YY

31/12/14

DD/MM/YYYY

31/12/2014

MM/DD/YY

12/31/14

MM/DD/YYYY

12/31/2014

MMM DD, YYYY

Dec 31, 2014

YYYYDDD

2014365

YYYY-MM-DD

2014-12-31

To create your own date display format, enter your choice in the Date Display Format text box using a combination of characters from Table 2 (assumes the default Day, Month, and Year format characters). The following guidelines apply:

  • The day, month, and year components can appear without spaces, or they can be separated using blank spaces or any punctuation mark.

  • The components can appear in any order.

  • One or two of the three components can be omitted.

  • The components cannot be less than two characters long.

  • You must use uppercase characters to specify the format.

  • The entire date display format, including any spaces and punctuation marks, cannot exceed 12 characters.

Table 2. Date display format characters

Specify This Display Format

To Display This

DD

Day (1 – 31)

DDD

Day (1 – 366)

MM

Month (1 – 12)

MMM

Month (Jan – Dec)

YY

Year (00 – 99)

YYYY

Year (1900 – 9999)

Note

If you specify a date display format that does not display all the available source data, quick filtering by date or datetime is disabled. For example, if you specify the format MMM YYYY for dates that have day, month, and year data, quick filtering on a date or datetime value in a view returns zero results.

Changes to the date display format are recorded in the log using the following syntax:

SET DATE value

Start of Century

Many data files use only two digits to represent the year, which means the century in which the year occurs is unspecified. The two-digit year denoting the earliest year assigned to the 20th century can vary from one set of data files to the next. This year is often called the start-of-century year or the pivot year.

The pivot year applies to two-digit years only, and does not affect data that uses four digits to represent the year. ACL can read four-digit years from 1900 to 9999.

The default Start of Century setting is 40. With this setting, ACL interprets two-digit years 40 to 99 as 1940 to 1999, and two-digit years 00 to 39 as 2000 to 2039.

To change the pivot year, enter a number from 0 to 99. For example, if you want to set 1950 as the pivot year, enter 50 in the Start of Century text box. The table below provides examples of different pivot years.

Start of Century setting

Year in source data

Interpreted as

00

00 to 99

1900 to 1999

40

40 to 99, 00 to 39

1940 to 1999, 2000 to 2039

50

50 to 99, 00 to 49

1950 to 1999, 2000 to 2049

99

99, 00 to 98

1999, 2000 to 2098

When working with data files that use a different pivot year from the Start of Century year, you can use an expression to create a computed field that correctly interprets the two-digit year or converts it to a four-digit year.

Changes to the Start of Century setting are recorded in the log using the following syntax:

SET CENTURY value

Aging Periods

This option sets the default aging periods for the Age dialog box. If you use a specific set of aging periods frequently, you can enter the set in the Aging Periods text box and ACL uses the setting as the default aging periods in the Age dialog box. If necessary, you can still override the periods in the Age dialog box.

Enter the periods in days, separated by commas without spaces. You can set as many aging periods as you want.

Changes to this setting are recorded in the log using the following syntax:

SET PERIODS values

Abbreviations for Month Names

This option sets the default abbreviations for MMM-format month names. Month abbreviations must be three characters long, in the correct order starting with January, and separated by commas without spaces.

This option affects the way ACL reads dates from a table, and displays dates in views, reports, and output files. For example, if MMM-format dates in the input file are in French, you would specify French month abbreviations:

Jan,Fév,Mar,Avr,Mai,...

ACL would then correctly interpret the string Fév as the second month, and Avr as the fourth. If you also choose a Date Display Format that uses MMM to display the month, ACL uses the abbreviations you provide to display the abbreviated month names in views, reports, and output files.

Changes to this setting are recorded in the log using the following syntax:

SET MONTHS values

Time Settings

Hour, Minute, Second

Use the Hour, Minute, and Second text boxes to specify the characters that represent these components of time and datetime formats. The default values are ‘h’ for Hour, ‘m’ for Minute, and ‘s’ for Second, but you can specify different characters for languages other than English. The characters you specify must be lowercase, they must all be different, and ‘h’, ‘m’, and ‘s’ can only be used in their default positions.

Time Display Format

This option lets you specify how ACL displays times, and the time portion of datetimes, in views, reports, and exported files. You can select one of the formats in the Time Display Format drop-down list, or you can create your own time display format.

Note

This option has no effect on how ACL reads times from data sources. To specify how ACL reads times, use the Data Definition Wizard, or the Format field in the Table Layout dialog box. For more information, see Formats of date and time source data.

The format options in the Time Display Format drop-down list are shown in Table 3.

Table 3. Formats in the Time Display Format drop-down list

Select This Display Format

To Display This

To Display This

hh:mm

23:59

11:59

hh:mm P

11:59 P

11:59 A

hh:mm PM

11:59 PM

11:59 AM

hh:mm:ss

23:59:59

11:59:59

hh:mm:ss P

11:59:59 P

11:59:59 A

hh:mm:ss PM

11:59:59 PM

11:59:59 AM

hh:mm:ss±hh:mm

23:59:59-05:00

11:59:59-05:00

To create your own time display format, enter your choice in the Time Display Format text box using a combination of characters from Table 4 (assumes the default Hour, Minute, and Second format characters). The following guidelines apply:

  • The hour, minute, and seconds components can appear without spaces, or they can be separated using blank spaces or any punctuation mark.

  • The components must appear in hour, minutes, and seconds order.

  • The seconds component can be omitted. The hour and minutes components cannot be omitted.

  • The components must be two characters long.

  • You must use lowercase characters to specify the format.

  • Including the optional AM/PM indicator switches the time display from the 24-hour clock to the 12-hour clock. The AM/PM indicator can be positioned anywhere after the hour component, and can be prefaced by a space, if desired.

  • The UTC offset must be prefaced with either a plus sign (+) or a minus sign (-).

  • The minutes component can be omitted from the UTC offset. Do not omit the minutes component if any of the time data you are displaying contains UTC offsets that are not whole hours.

  • The entire time display format, including any spaces, punctuation marks, and the plus or minus sign, cannot exceed 14 characters.

Table 4. Time display format characters

Specify This Display Format

To Display This

hh

Hour (00 – 23)

mm

Minute (00 – 59)

ss

Second (00 – 59)

A or P

AM/PM indicator (A and P)

AM or PM

AM/PM indicator (AM and PM)

+ or -

UTC offset indicator (+ and -)

Note

If you specify a time display format that does not display all the available source data, quick filtering by datetime or time is disabled. For example, if you specify the format hh:mm for times that have hour, minute, and seconds data, quick filtering on a datetime or time value in a view returns zero results.

Changes to the time display format are recorded in the log using the following syntax:

SET TIME value

Display Times with UTC Offset as UTC

ACL can accept local time data that includes a UTC offset (explained below), such as 10:30:15-05:00 (-05:00 is the UTC offset). UTC is Coordinated Universal Time, the time at zero degrees longitude, and the UTC offset is a time zone indicator.

The Display Times with UTC Offset as UTC option lets you specify whether ACL converts the local time to UTC without a UTC offset (the default setting), or displays UTC-based data as local time with a UTC offset. For example, here are the two different ways of displaying the same piece of UTC-based data:

  • 31/12/2014 15:30:15

    (Display Times with UTC Offset as UTC checked, default setting)

  • 31/12/2014 10:30:15-05:00

    (Display Times with UTC Offset as UTC unchecked)

When Display Times with UTC Offset as UTC is checked, ACL incorporates the UTC offset in the main piece of time data, and adjusts the main piece of time data by an appropriate number of hours. In the example above, conversion to UTC increments the local time data by 5 hours.

Conversion of local time to UTC is for display purposes only, and does not affect the source data, which continues to contain the UTC offset. You can change back and forth between the two different display modes whenever you want to.

When ACL performs calculations on local time data with a UTC offset, the UTC offset is automatically incorporated and the calculation is performed on the UTC equivalent of the local time. If Display Times with UTC Offset as UTC is checked, you see the actual time data that is being used in a calculation, which can make the results easier to understand. For more information, see How UTC offsets affect datetime expressions.

About UTC

UTC is a global time standard that has replaced Greenwich Mean Time (GMT). For most purposes, the two standards are equivalent. The final portion of UTC-based time data (for example, -05:00, or +01:00) is a UTC offset that indicates how far behind or ahead the local time value is compared to UTC. For example:

  • 31/12/2014 10:30:15-05:00 represents December 31, 2014, 10:30:15 AM, Eastern Standard Time (North America).

  • 31/12/2014 15:30:15 (UTC) represents the same point in time at zero degrees longitude.

For UTC-based datetime data, if conversion to UTC goes forward or backward across the boundary of midnight, the date is adjusted by one day.

Note

The UTC offset is also referred to as the time zone offset, although the two are not exactly the same. More than one time zone can have the same UTC offset.

How ACL displays UTC-based and non-UTC time data

Examples of how ACL displays both UTC-based and non-UTC time data appear in Table 5 and Table 6 below. A single ACL table can contain both UTC-based and non-UTC time data.

Table 5. UTC-based time data

Source data

‘Display Times with UTC Offset as UTC’ selected (default setting)

‘Display Times with UTC Offset as UTC’ not selected

Time Display Format = hh:mm:ss

31/12/2014 10:30:15-05:00

31/12/2014 15:30:15

31/12/2014 10:30:15-05:00

01/01/2015 00:30:15+01:00

31/12/2014 23:30:15

01/01/2015 00:30:15+01:00

Time Display Format = hh:mm:ss+hh:mm

31/12/2014 10:30:15-05:00

31/12/2014 15:30:15+00:00

31/12/2014 10:30:15-05:00

01/01/2015 00:30:15+01:00

31/12/2014 23:30:15+00:00

01/01/2015 00:30:15+01:00

Table 6. Non-UTC time data

Source data

‘Display Times with UTC Offset as UTC’ selected (default setting)

‘Display Times with UTC Offset as UTC’ not selected

Time Display Format = hh:mm:ss

31/12/2014 10:30:15

31/12/2014 10:30:15

31/12/2014 10:30:15

Time Display Format = hh:mm:ss+hh:mm

31/12/2014 10:30:15

31/12/2014 10:30:15+00:00

31/12/2014 10:30:15+00:00

Related concepts
Formats of date and time source data
Related tasks
Configuring ACL options


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