You can use expressions to perform calculations with dates, datetimes, and times:
Calculate elapsed days, elapsed days and time, or elapsed time
For example, `20141231`  `20141130` returns 31, the number of days between the two dates.
Make positive or negative adjustments to dates, datetimes, or times
For example, `20141231`  15 returns 16 Dec 2014, the date 15 days earlier.
Compare dates, datetimes, or times
For example, `20141231 183000` > `20141231 171500` returns T (True), because the first datetime is more recent than the second datetime.
The following sections explain different aspects of using datetimes in expressions and provide a number of examples:
To help you work with datetime data, ACL provides a number of date and time functions that perform a variety of useful tasks. You can use these functions when constructing datetime expressions.
The datetime functions, grouped by task, appear in Table 1. You can see the same list of functions if you filter the Functions dropdown list in the Expression Builder by Date & Time.
For a detailed explanation of each datetime function, see the ACL Language Reference.
Task performed 
Functions 

Returns the age, in days, of a date compared to a cutoff date, or the current date 
AGE( ) 
Calculates a date, a month end, or a month beginning, a specified number of months before or after a date 
EOMONTH( ), GOMONTH( ) 
Identifies the day of the week, or the month of the year, for a date 
CDOW( ), CMOY( ) 
Returns a numeric value (1 to 7) representing the day of the week for a date 
DOW( ) 
Extracts the date or the time from a datetime value 
DATE( ), TIME( ) 
Extracts the day, month, year, hour, minutes, or seconds from a datetime value 
DAY( ), MONTH( ), YEAR( ), HOUR( ), MINUTE( ), SECOND( ) 
Converts serial datetime values, or character or numeric datetime values, to regular datetime values with a Datetime data type 
STOD( ), STODT( ), STOT( ), CTOD( ), CTODT( ), CTOT( ) 
Returns the current operating system date, datetime, or time 
TODAY( ), DATETIME( ), NOW( ) 
As you work with datetimes in expressions, it is important to differentiate between amounts of time, and points in time, because the difference requires you to construct different types of expressions.
The time value 08:30:00 could refer to an amount of time – 8 hours and 30 minutes – or a point in time – 08:30:00 AM in the morning. If you subtract one time from another time the result is an elapsed time, which is an amount of time. For example:
STOT(`T083000`  `T071500`) returns 01:15:00 (1 hour, 15 minutes, 0 seconds)
If you add a number to a time, or subtract a number from a time, the result is a positive or negative adjustment that creates another point in time – either later or earlier than the initial time. For example:
`T083000`  (1.00000000/24*1.5) returns 07:00:00 AM
The Time Display Format ( ) in the first example is hh:mm:ss and in the second example is hh:mm:ss PM. Both examples involve serial datetime calculations, which are explained in subsequent sections.
Similarly, if you subtract one date from another date the result is an elapsed number of days, which is an amount of time. For example:
`20141231`  `20141130` returns 31, the number of days between the two dates
If you add a number to a date, or subtract a number from a date, the result is another point in time – either more recent or earlier than the initial date. For example:
`20141231`  31 returns 30 Nov 2014, the date 31 days earlier
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:
Any combination of date, datetime, or time values can be used in a subtraction operation or a comparison operation.
Whole numbers, mixed numbers, and fractional numbers can be subtracted from or added to date, datetime, or time values.
Date, datetime, or time values cannot be added to one another.
If you need to add amounts of time, such as the hours worked in a week, you can use ACL functions to extract the hour, minutes, and seconds portions of times as numeric values. You can then perform calculations on those numeric values.
Date, datetime, or time values cannot be compared to numbers.
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 2 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.
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 
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) 
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 24hour 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) 
`141231t2359590500` 

(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` 
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.
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:
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:
`t120000` + `t030000`
`20141231 235959` + `t030000`
You can create a valid expression by adding the serial time equivalent of 3 hours (0.125), but manually creating such expressions can be awkward because it requires that you know what the serial time equivalent is:
`t120000` + 0.125 returns 15:00:00
`20141231 235959` + 0.125 returns 01 Jan 2015 02:59:59
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 that must be converted to a regular datetime or time value in order to be more human readable:
`t120000`  `t030000` returns 0.37500000000000
STOT(`t120000`  `t030000`) returns 09:00:00
The following method allows you to make positive or negative adjustments to datetime or time values more easily:
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 21/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.
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 + 21/2 hours.
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 21/2 hours.
Table 3 provides additional examples of this method.
Table 3 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.
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 Assumes a current ACL time display format of hh:mm:ss 
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. 