Using datetimes in expressions

You can use expressions to perform calculations with dates, datetimes, and times:

The following sections explain different aspects of using datetimes in expressions and provide a number of examples:

Valid and invalid datetime expressions

Datetime expressions encompass a number of possible combinations of datetime subtype (date, datetime, and time) and operator. Not all combinations are valid expressions. For example, you can subtract one date from another to find out the number of elapsed days, but you cannot add two dates because the operation is illogical. You can, however, add a number to a date to produce a subsequent date.

The following rules apply to datetime expressions:

Even if an expression is valid, it may not always serve a useful analytical purpose. For example, ACL will process the expression Finish_Date > Start_Time, but the result is always True (T) and comparing a date to a time serves no logical purpose.

Table 1 summarizes the combinations that are possible with datetime expressions and indicates whether each combination is valid or invalid – that is, whether it can be processed by ACL.

Table 1. Combinations possible with datetime expressions

Date value

Datetime value

Time value

Number

Date value

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Add

Invalid:

Add

Invalid:

Add

Invalid:

Add

Invalid:

Compare

Datetime value

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Add

Invalid:

Add

Invalid:

Add

Invalid:

Add

Invalid:

Compare

Time value

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Compare

Valid:

Subtract

Add

Invalid:

Add

Invalid:

Add

Invalid:

Add

Invalid:

Compare

Data types returned by datetime expressions

The data type of the result returned by a datetime expression depends on the values and operator in the expression:

Datetime expression

Data type of result

Subtraction (datetime values only)

Any combination of date, datetime, or time values used in a subtraction operation

Numeric

A serial date, serial datetime, or serial time

For more information, see About serial datetimes.

Addition or subtraction (datetime values and numbers)

Whole number, mixed number, or fractional number subtracted from or added to a date, datetime, or time value

Datetime

A date, datetime, or time subtype of the Datetime data type

Comparison (datetime values only)

Any combination of date, datetime, or time values used in a comparison operation

Logical

T (True) or F (False)

Format of datetime literals

When specifying a literal date, datetime, or time value in an expression you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231`. For datetime values, you can use any combination of date, separator, and time formats. The date must precede the time, and you must use a separator between the two. For time values, you can use any combination of separator and time formats. You must use a separator before the time value for the expression to operate correctly. Times must be specified using the 24-hour clock. Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-). Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

Date formats

Separator formats

Time formats

Examples

YYYYMMDD

single blank space

hhmmss

`20141231`

YYMMDD

the letter ‘t’

hhmm

`20141231 235959`

the letter ‘T’

hh

`141231 2359`

 

+/-hhmm

(UTC offset)

`141231t23`

+/-hh

(UTC offset)

`141231t235959-0500`

(Note: Do not use hh alone in the main time format with data that has a UTC offset. For example, avoid: hh+hhmm. Results can be unreliable.)

`141231T2359+01`

`t235959`

Date and time functions

To help you work with datetime data, ACL provides a number of date and time functions you can use in datetime expressions. You can use these functions to perform a variety of useful tasks, including the following:

For more information about ACL’s date and time functions, see the ACL Language Reference.

Using functions to add amounts of time

You cannot directly add time values to one another in ACL. However, you can use ACL functions to extract the hour, minutes, and seconds portions of times as numeric values and then perform calculations on those numeric values. The example of timesheet data below illustrates this approach using hours and minutes.

Figure 1. Timesheet data totaled using functions

Several computed fields are required to produce the calculations:

Computed field name

Expression

Description

Elapsed

STOT(End_Time - Start_Time)

A subtraction operation that calculates the hours worked for the day. The STOT( ) function converts the results from serial time values to regular time values.

Hours

HOUR(Elapsed)

The hour portion extracted as a numeric value from the Elapsed value.

Minutes

MINUTE(Elapsed)

The minutes portion extracted as a numeric value from the Elapsed value.

(For display purposes. Not required for the calculation.)

Part Hours

MINUTE(Elapsed)/60.000

The minutes portion extracted as a numeric value from the Elapsed value, and calculated as a decimal fractional portion of 60 minutes.

Hours+Part Hours

Hours + Part_Hours

The numeric hours and part hours added together.

As a final step, you can total the Hours+Part Hours field to calculate the total hours for the week:



Making positive or negative adjustments to dates, datetimes, or times

You can make a positive or negative adjustment to date, datetime, or time values – for example, adding or subtracting 15 days, or adding or subtracting 3 hours. Making a positive or negative adjustment to a date is straightforward. You add or subtract the required number of days to calculate one date based on another:

Making a positive or negative adjustment to a datetime or a time value is somewhat more involved. You cannot directly add time values to each other, or a time value to a datetime value. For example, if you try to make a positive adjustment of 3 hours using either of the following expressions, you will get an ‘Expression type mismatch’ error:

You can create a valid expression by adding the serial time equivalent of 3 hours, but manually creating such expressions can be awkward because it requires that you know what the serial time equivalent is:

Making a negative adjustment to a datetime or time value is easier to do, however the result is a serial datetime value or a serial time value which must be converted to a regular datetime or time value in order to be more human readable:

The following method allows you to make positive or negative adjustments to datetime or time values more easily:

  1. Create a computed field that calculates the serial time equivalent of the amount of time you want to add or subtract.

    For example: (1.00000000/24*2.5) produces the serial time equivalent of 2-1/2 hours (0.10416668). You need to specify ‘1’ with the multiple zeros in the decimal places to ensure ACL does not round the result. You can change the number you multiple by to get an appropriate number of hours: (1.00000000/24*1), (1.00000000/24*8), (1.00000000/24*10.25), etc.

  2. In the same computed field, add or subtract the calculated serial time to or from the source time or datetime value.

    For example: <time or datetime field> + (1.00000000/24*2.5) = values in field + 2-1/2 hours.

  3. If you want to add or subtract both days and time to or from a datetime value, include an appropriate number of days in the calculation.

    For example: <datetime field> + 2 + (1.00000000/24*2.5) = values in field + 2 days and 2-1/2 hours.

Table 2 provides additional examples of this method.

Examples of datetime expressions

Table 2 provides examples of valid datetime expressions.

In a number of the examples, the results are returned as serial datetimes – that is, a date, datetime, or time value represented as an integer or a decimal fractional portion of 24 hours. You can use the STOD( ), STODT( ), and STOT( ) functions to convert serial datetime results into regular datetime values. For more information, see About serial datetimes.

Table 2. Examples of datetime expressions

Expression

Result

Calculate elapsed days, days and time, or time

`20141231` - `20141130`

31

The elapsed days between the two dates

Finish_Date - Start_Date

The elapsed days between Finish_Date and Start_Date values

`20141231 235959` - `20141130 114530`

31.51005787037037

The elapsed days and time between the two datetimes, expressed as a serial datetime

STRING(INT(`20141231 235959` - `20141130 114530`),5) + " " + TIME(STOT(MOD(`20141231 235959` - `20141130 114530`, 1)))

31 12:14:29

The elapsed days and time between the two datetimes in the example above, expressed as days, hours, minutes, and seconds

Finish_Datetime - Start_Datetime

The elapsed days and time between Finish_Datetime and Start_Datetime values, expressed as a serial datetime

STRING(INT(Finish_DateTime - Start_DateTime), 5) + " " + TIME(STOT(MOD(Finish_DateTime - Start_DateTime, 1)))

The elapsed days and time between Finish_Datetime and Start_Datetime values, expressed as days, hours, minutes, and seconds

`T235959` - `T114530`

0.51005787037037

The elapsed time between the two times, expressed as a serial time

STOT(0.51005787037037)

12:14:29

The serial time in the example above converted to a time value, using the current ACL time display format

STOT(`T235959` - `T114530`)

12:14:29

The elapsed time between the two times, expressed as a time using the current ACL time display format

Finish_Time - Start_Time

The elapsed times between Finish_Time and Start_Time values, expressed as a serial time

Make positive or negative adjustments to dates, datetimes, or times

Due_date + 15

The values in the Due_date field incremented by 15 days

`20141231` - 15

16 Dec 2014

The date decremented by 15 days. Assumes a current ACL date display format of DD MMM YYYY

`20141231 235959` + (1.00000000/24*1.5)

01 Jan 2015 01:29:59

The datetime plus 1.5 hours

`20141231 235959` - (1.00000000/24*1.5)

31 Dec 2014 22:29:59

The datetime minus 1.5 hours

STODT(`20141231 235959` - `T013000`)

31 Dec 2014 22:29:59

The datetime minus 1.5 hours

`20141231 235959` + 2 + (1.00000000/24*1.5)

03 Jan 2015 01:29:59

The datetime plus 2 days and 1.5 hours

`20141231 235959` - 2 - (1.00000000/24*1.5)

29 Dec 2014 22:29:59

The datetime minus 2 days and 1.5 hours

`t235959` + (1.00000000/24*1.5)

01:29:59

The time plus 1.5 hours

`T173000` - (1.00000000/24*1.5)

16:00:00

The time minus 1.5 hours

STOT(`T173000` - `T013000`)

16:00:00

The time minus 1.5 hours

STOT(STOT(`T173000` - `T013000`) - `T010000`)

15:00:00

The time minus 1.5 hours, minus another 1 hour

Compare dates, datetimes, or times

`20141231` > `20141230`

T (True)

Due_date <= `20141231`

All values in the Due_date field on or before 31 Dec 2014

Payment_date > Due_date

All values in the Payment_date field past their due date

CTOD(DATE(Payment_timestamp, "YYYYMMDD"), "YYYYMMDD") > Due_date

All values in the Payment_timestamp field past their due date

To compare datetime and date values, the date is first extracted as a character value from the datetime values in the Payment_timestamp field, and then converted back to a date value to compare it with the due date.

To ensure date formats match, identical formats are specified for the DATE( ) format parameter (the output format) and the CTOD( ) format parameter (the input format).

Login_time > `t100000`

All values in the Login_time field later than 10:00:00

Datetime expressions that use conversion functions

STOT(CTOT("t120000") - CTOT("t090000") )

03:00:00

The elapsed time between the two character time values

The character time values are first converted to regular time values so they can be used in a subtraction operation. The numeric serial time resulting from the subtraction operation is then converted to a regular time value.

CTOT(TIME(`20141231 125959`)) < `T235959`

T (True)

The time is first extracted as a character value from the datetime value, and then converted back to a time value to compare it with 23:59:59.

Section contents



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