Date, Time and Elapsed Time Constants (Literals)

Date and time fields are usually defined when you create a table layout. However, when creating expressions, you may need to enter a date and/or time value manually. You can use a date or time constant (or literal) in your expressions to specify a date or time that never changes, such as a date that indicate a fiscal or calendar year.

You can also enter elapsed time constants (literals).

The most common way to enter a date and/or time constant, or an elapsed time constant is via the Expression Builder.

Suppose you need to list all transactions before January 31, 2003. To do this, you must place the date constant in YYYYMMDD format between reverse quotes (using the reverse quote character located on the same key as the tilde ~ character on most keyboards).

For example, December 31, 2003 is entered surrounded by reverse quotes as:

‘20031231‘

No other format is valid.

Note: All date and time constants must be entered enclosed in reverse quotes. Date constants must be entered in the format YYYYMMDD while time constants must be entered in the format HH:MM:SS (with mandatory use of colons and 24 hour clock mode). Date Time combinations in the format YYYYMMDD HH:MM:SS are also supported. For more detail, see the relevant section below

Note: All elapsed time constants must be entered enclosed in reverse quotes in the form ‘e<D/>HH<:MM<:SS>>‘. For more detail, see the relevant section below.

Date Selector

Alternatively, you can click [Date] to use the date selector window. Click the arrow beside today’s date to see the calendar, then click the arrows, the month or the year to select another date.

Date Constants

Enclose date constants (literals) in reverse quotes in the form:

‘YYYYMMDD‘

For example, to specify December 31st, 2001, enter:

‘20011231‘

Note: The reverse quote character is located on the same key as the tilde ~ character on most keyboards.

Time Constants

Like date constants, time constants are also enclosed in reverse quotes. They must be entered using a 24 hour clock mode with the hour, month and seconds separated by colons. The format of a time constant (literal) is:

‘HH:MM:SS‘

For example, to specify 11:35 PM, enter:

‘23:35:00‘

Elapsed Time Constants (Literals)

You can also specify elapsed time constants, in terms of days, hours, minutes and seconds. The format for an elapsed time constant is:

‘e<D/>HH<:MM<:SS>>‘

The parameter “e” must be included to indicate elapsed time. The optional parameter, “D/” indicates a number of days. Hours must always be specified. Minutes and seconds are optional, however minutes must be specified for seconds to be included. Hours, minutes and seconds must be separated by colons (:).

For example, to specify an elapsed time constant of 4 days, 2 hours and 30 minutes, enter:

‘e4/02:30‘

Note: When you subtract two datetime values you get an elapsed difference, which may be expressed as just a number of days, a time, or both, as appropriate.

Date and Time Constants

To specify a combined data and time constant, the value must be specified in the format:

‘YYYYMMDD HH:MM:SS‘

The same rules are required for combined date time constants as are required for individual for date and time constants. For example, to specify December 31st, 2001 at 11:35 p.m., enter:

‘20011231 23:35:00‘

Note: When comparing a datetime field to a date constant with no time component, the missing time portion is assumed to be midnight (00:00:00).