User Functions

Calculates the difference between two dates and returns the number of units specified.

Possible choices for unit are: week, day, hour, minute, and second.

Enclose the unit in double or single quotes, or pass a variable which contains the unit name. The unit name is case sensitive.

Each date must be specified using the date/time prefix followed by a valid string representation of a date, or it must be an OLE automation date/time. If date2 is greater than date1, the result will be positive. This number might include whole and fractional units.

Example: datediff(“hour”, #”4:30 PM”, #”2:00 PM”)

Returns: -2.5

Adds the specified number of units to the given date and returns a new date as an OLE automation date/time.

Possible choices for unit are: year, month, week, day, hour, minute, and second.

Enclose the unit in double or single quotes, or pass a variable which contains the unit name. The unit name is case sensitive.

The date must be specified using the date/time prefix followed by a valid string representation of a date, or it must be an OLE automation date/time. The value may be fractional.

Example: {dateadd("minute",#"4:30 PM",2.5).astime}

Returns: 4:32:30 PM

Converts the specified date string to an OLE automation date/time. If the datestr cannot be converted, the return is 0.

Example: {dateparse("14:30").astime}

Returns: 2:30:00 PM

Formats the date using the specified format string. The date must be specified using the date/time prefix followed by a valid string representation of a date, or it must be an OLE automation date/time. See the section titled "Format Specifiers".

Example: {datefmt(now, "yyyy-MMM-d, h:mm")}

Returns: 2021-Oct-14, 11:48

Returns a portion of the date or time. The date must be specified using the date/time prefix followed by a valid string representation of a date, or it must be an OLE automation date/time. The unit specifies the date part to return.

Possible choices for unit are: year, month, day, hour, minute, second, and dow.

Enclose the unit in double or single quotes, or pass a variable which contains the unit name. The unit name is case sensitive.

[String expression] Returns the string representation of the specified date part. Month returns the 3 character month abbreviation. Year returns the full 4 digit year. Hour returns the hour in 24 hour format. All numeric values, except year, are prefixed with a 0 if there are less than two digits. Dow stands for Day of Week and is returned as the full string representation of the week day, unabbreviated.

[Numeric expression] Returns the numeric representation of the specified date part. Year returns the full 4 digit year. Hour returns the hour in 24 hour format. Dow stands for Day of Week and is returned as the ordinal value for the week day where Sunday is 0.

Example:

ASSIGN dy=#"11/5/08 6:00PM"

ASSIGN dp=datepart("year", dy)

[String expression] Returns a date formatted in standard date/time format.

[Numeric expression] Returns a date as an OLE automation date/time.

[String expression] Returns a date/time formatted in standard date/time format.

[Numeric expression] Returns a date/time as an OLE automation date/time.

Returns a character from the ASCII code value. This allows a script the ability to programmatically add special characters to a string – such as carriage returns and tabs.

Be cautious with this function as char(0) through char(7) are reserved for internal use and MAY cause scripts to malfunction.

Inline-If – returns either trueval or falseval depending on the value of compare.

If compare is non-zero (true), the trueval will be returned.

If compare is zero (false), the falseval will be returned.

Both trueval and falseval must be numeric values or expressions. Strings cannot be used. See also iifs.

String Inline-If – returns either truestr or falsestr depending on the value of compare.

If compare is non-zero (true), the truestr will be returned.

If compare is zero (false), the falsestr will be returned.

This function is identical to iif except it expects strings and returns a string.

In a snippet you can set the savetodb property for a variable.

For example, Assign variable='123123123'

variable.savetodb(1)

Determines if a Dynamic Data object or a property of an object is null (undefined or 0). If used with an Assign, a boolean value is returned, indicating whether or not the defined object or property is null

Example:

DYNAMIC obj
ASSIGN obj.prop1 = "val1"
ASSIGN obj.prop2 = "val2"
		
ASSIGN myVar = !isnull(obj)
IF !isnull(obj)                // If "obj" is not null ...
{
ASSIGN isItNull1 = false
}
ELSE
{
ASSIGN isItNull1 = true
}

IF !isnull(obj.prop1)        // If property "prop1" of "obj" is not null ...
{
ASSIGN isItNull2 = false
}
ELSE
{
ASSIGN isItNull2 = true
}

IsNull can only be used with objects--attempting to use it on a variable in a script will cause a script error. The following is not a valid use of IsNull, whether or not NewVar has been previously referenced or defined as shown on the first line.

Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value.

If the f format specifier is used without other format specifiers, it is interpreted as the f standard date and time format specifier.

When used as part of a format string supplied to the ParseExact, TryParseExact, ParseExact, or TryParseExact methods, the number of f format specifiers used indicates the number of most significant digits of the seconds fraction that must be present to successfully parse the string.

Represents the four most significant digits of the seconds fraction; that is, it represents the ten thousandths of a second in a date and time value.

While it is possible to display the ten thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the five most significant digits of the seconds fraction; that is, it represents the hundred thousandths of a second in a date and time value.

While it is possible to display the hundred thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the six most significant digits of the seconds fraction; that is, it represents the millionths of a second in a date and time value.

While it is possible to display the millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the seven most significant digits of the seconds fraction; that is, it represents the ten millionths of a second in a date and time value.

While it is possible to display the ten millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value. Nothing is displayed if the digit is zero.

If the F format specifier is used without other format specifiers, it is interpreted as the F standard date and time format specifier.

The number of F format specifiers used with the ParseExact, TryParseExact, ParseExact, or TryParseExact methods indicates the maximum number of most significant digits of the seconds fraction that can be present to successfully parse the string.

Represents the four most significant digits of the seconds fraction; that is, it represents the ten thousandths of a second in a date and time value. However, trailing zeros or four zero digits are not displayed.

While it is possible to display the ten thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the five most significant digits of the seconds fraction; that is, it represents the hundred thousandths of a second in a date and time value. However, trailing zeros or five zero digits are not displayed.

While it is possible to display the hundred thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the six most significant digits of the seconds fraction; that is, it represents the millionths of a second in a date and time value. However, trailing zeros or six zero digits are not displayed.

While it is possible to display the millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the seven most significant digits of the seconds fraction; that is, it represents the ten millionths of a second in a date and time value. However, trailing zeros or seven zero digits are not displayed.

While it is possible to display the ten millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.

Represents the AM/PM designator as defined in the current DateTimeFormatInfo.AMDesignator or DateTimeFormatInfo.PMDesignator property. The AM designator is used if the hour in the time being formatted is less than 12. Otherwise, the PM designator is used.

Your application should be sure to use the tt specifier for languages for which it is necessary to maintain the distinction between AM and PM. An example is Japanese, for which the AM and PM designators differ in the second character instead of the first character.

Represents the year with a minimum of three digits. If the year has more than three significant digits, they are included in the result string. If the year has fewer than three digits, the number is padded with leading zeros to achieve three digits.

Represents the year as a four-digit number. If the year has more than four digits, only the four low-order digits appear in the result. If the year has fewer than four digits, the number is padded with leading zeros to achieve four digits.

Represents the year as a five-digit number. If the year has more than five digits, only the five low-order digits appear in the result. If the year has fewer than five digits, the number is padded with leading zeroes to achieve five digits.

If there are additional y specifiers, the number is padded with as many leading zeroes as necessary to achieve the number of y specifiers.

With DateTime values, represents the signed offset of the local operating system's time zone from Coordinated Universal Time (UTC), measured in hours. It does not reflect the value of an instance's Kind property. For this reason, the z format specifier is not recommended for use with DateTime values.

With DateTimeOffset values, represents the DateTimeOffset value's offset from UTC in hours.

The offset is always displayed with a leading sign. A plus sign (+) indicates hours ahead of and a minus sign (-) indicates hours behind UTC. The offset ranges from –12 through +13. A single-digit offset is formatted without a leading zero.

With DateTime values, represents the signed offset of the local operating system's time zone from UTC, measured in hours. It does not reflect the value of an instance's Kind property. For this reason, the zz format specifier is not recommended for use with DateTime values.

With DateTimeOffset values, represents the DateTimeOffset value's offset from UTC in hours.

The offset is always displayed with a leading sign. A plus sign (+) indicates hours ahead of and a minus sign (-) indicates hours behind UTC. The offset ranges from –12 through +13. A single-digit offset is formatted with a leading zero.

With DateTime values, represents the signed offset of the local operating system's time zone from UTC, measured in hours and minutes. It does not reflect the value of an instance's Kind property. For this reason, the zzz format specifier is not recommended for use with DateTime values.

With DateTimeOffset values, represents the DateTimeOffset value's offset from UTC in hours and minutes.

The offset is always displayed with a leading sign. A plus sign (+) indicates hours ahead of and a minus sign (-) indicates hours behind UTC. The offset ranges from –12 through +13. A single-digit offset is formatted with a leading zero.

Represents the result associated with a c custom format specifier, when the custom date and time format string consists solely of that custom format specifier.

That is, to use the d, f, F, h, m, s, t, y, z, H, or M custom format specifier by itself, the application should specify %d, %f, %F, %h, %m, %s, %t, %y, %z, %H, or %M.

Currency

The number is converted to a string that represents a currency amount. The conversion is controlled by the currency format information of the current NumberFormatInfo object.

The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default currency precision given by the current NumberFormatInfo object is used.

Decimal

This format is supported only for integral types. The number is converted to a string of decimal digits (0-9), prefixed by a minus sign if the number is negative.

The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier.

Scientific (exponential)

The number is converted to a string of the form "-d.ddd…E+ddd" or "-d.ddd…e+ddd", where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. One digit always precedes the decimal point.

The precision specifier indicates the desired number of digits after the decimal point. If the precision specifier is omitted, a default of six digits after the decimal point is used.

The case of the format specifier indicates whether to prefix the exponent with an 'E' or an 'e'. The exponent always consists of a plus or minus sign and a minimum of three digits. The exponent is padded with zeros to meet this minimum, if required.

Fixed-point

The number is converted to a string of the form "-ddd.ddd…" where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative.

The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision is given by the NumberDecimalDigits property of the current NumberFormatInfo object.

General

The number is converted to the most compact of either fixed-point or scientific notation, depending on the type of the number and whether a precision specifier is present. If the precision specifier is omitted or zero, the type of the number determines the default precision, as indicated by the following list.

Byte or SByte: 3

Int16 or UInt16: 5

Int32 or UInt32: 10

Int64 or UInt64: 19

Single: 7

Double: 15

Decimal: 29

Fixed-point notation is used if the exponent that would result from expressing the number in scientific notation is greater than -5 and less than the precision specifier; otherwise, scientific notation is used. The result contains a decimal point if required and trailing zeroes are omitted. If the precision specifier is present and the number of significant digits in the result exceeds the specified precision, then the excess trailing digits are removed by rounding.

The exception to the preceding rule is if the number is a Decimal and the precision specifier is omitted. In that case, fixed-point notation is always used and trailing zeroes are preserved.

If scientific notation is used, the exponent in the result is prefixed with 'E' if the format specifier is 'G', or 'e' if the format specifier is 'g'.

Number

The number is converted to a string of the form "-d,ddd,ddd.ddd…", where '-' indicates a negative number symbol if required, 'd' indicates a digit (0-9), ',' indicates a thousand separator between number groups, and '.' indicates a decimal point symbol. The actual negative number pattern, number group size, thousand separator, and decimal separator are specified by the NumberNegativePattern, NumberGroupSizes, NumberGroupSeparator, and NumberDecimalSeparator properties, respectively, of the current NumberFormatInfo object.

The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision is given by the NumberDecimalDigits property of the current NumberFormatInfo object.

Percent

The number is converted to a string that represents a percent as defined by the NumberFormatInfo.PercentNegativePattern property if the number is negative, or the NumberFormatInfo.PercentPositivePattern property if the number is positive. The converted number is multiplied by 100 in order to be presented as a percentage.

The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the current NumberFormatInfo object is used.

Round-trip

This format is supported only for the Single and Double types. The round-trip specifier guarantees that a numeric value converted to a string will be parsed back into the same numeric value. When a numeric value is formatted using this specifier, it is first tested using the general format, with 15 spaces of precision for a Double and 7 spaces of precision for a Single. If the value is successfully parsed back to the same numeric value, it is formatted using the general format specifier. However, if the value is not successfully parsed back to the same numeric value, then the value is formatted using 17 digits of precision for a Double and 9 digits of precision for a Single.

Although a precision specifier can be present, it is ignored. Round trips are given precedence over precision when using this specifier.

Hexadecimal

This format is supported only for integral types. The number is converted to a string of hexadecimal digits. The case of the format specifier indicates whether to use uppercase or lowercase characters for the hexadecimal digits greater than 9. For example, use 'X' to produce "ABCDEF", and 'x' to produce "abcdef".

The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier.

Zero placeholder

If the value being formatted has a digit in the position where the '0' appears in the format string, then that digit is copied to the result string; otherwise, a '0' appears in the result string. The position of the leftmost '0' before the decimal point and the rightmost '0' after the decimal point determines the range of digits that are always present in the result string.

The "00" specifier causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used.

For example, formatting 34.5 with "00" would result in the value 35. The following example displays several values formatted using custom format strings that include zero placeholders.

Digit placeholder

If the value being formatted has a digit in the position where the '#' appears in the format string, then that digit is copied to the result string. Otherwise, nothing is stored in that position in the result string.

The "##" format string causes the value to be rounded to the nearest digit preceding the decimal, where rounding away from zero is always used.

For example, formatting 34.5 with "##" would result in the value 35. The following example displays several values formatted using custom format strings that include digit placeholders.

Decimal point

The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored.

The actual character used as the decimal separator in the result string is determined by the NumberDecimalSeparator property of the NumberFormatInfo object that controls formatting.

Thousand separator and number scaling

The ',' character serves as both a thousand separator specifier and a number scaling specifier.

Thousand separator specifier: If one or more ',' characters is specified between two digit placeholders (0 or #) that format the integral digits of a number, a group separator character is inserted between each number group in the integral part of the output.

The NumberGroupSeparator and NumberGroupSizes properties of the current NumberFormatInfo object determine the character used as the number group separator and the size of each number group. For example, if the string "#,#" and the invariant culture are used to format the number 1000, the output is "1,000".

Number scaling specifier: If one or more ',' characters is specified immediately to the left of the explicit or implicit decimal point, the number to be formatted is divided by 1000 each time a number scaling specifier occurs. For example, if the string "0,," is used to format the number 100 million, the output is "100".

You can use thousand separator and number scaling specifiers in the same format string. For example, if the string "#,0,," and the invariant culture are used to format the number one billion, the output is "1,000".

Percentage placeholder

The presence of a '%' character in a format string causes a number to be multiplied by 100 before it is formatted. The appropriate symbol is inserted in the number itself at the location where the '%' appears in the format string.

The percent character used is dependent on the current NumberFormatInfo class.

E0

E+0

E-0

e0

e+0

e-0

Scientific notation

If any of the strings "E", "E+", "E-", "e", "e+", or "e-" are present in the format string and are followed immediately by at least one '0' character, then the number is formatted using scientific notation with an 'E' or 'e' inserted between the number and the exponent.

The number of '0' characters following the scientific notation indicator determines the minimum number of digits to output for the exponent. The "E+" and "e+" formats indicate that a sign character (plus or minus) should always precede the exponent. The "E", "E-", "e", or "e-" formats indicate that a sign character should only precede negative exponents.

Escape character

In C# and C++, the backslash character causes the next character in the format string to be interpreted as an escape sequence. It is used with traditional formatting sequences like '\n' (new line).

In some languages, the escape character itself must be preceded by an escape character when used as a literal.

Otherwise, the compiler interprets the character as an escape sequence.

Use the string "\\" to display '\'.

"abc"

'abc'

Literal string

Characters enclosed in single or double quotes are copied to the result string, and do not affect formatting.

Section separator

The ';' character is used to separate sections for positive, negative, and zero numbers in the format string.

If there are two sections in the custom format string, the leftmost section defines the formatting of positive and zero numbers, while the rightmost section defines the formatting of negative numbers.

If there are three sections, the leftmost section defines the formatting of positive numbers, the middle section defines the formatting of zero numbers, and the rightmost section defines the formatting of negative numbers.

All other characters

Any other character is copied to the result string, and does not affect formatting.