Functions

absolute
Syntax: absolute (numeric_exp)
Returns the absolute value of numeric_exp. The sign of negative values is changed to positive. Examples: absolute (5) returns 5; absolute (-5) returns 5.

add-days
Syntax: add-days (date_exp, integer_exp)
Returns the datetime resulting from adding integer_exp days to date_exp.

add-months
Syntax: add-months (date_exp, integer_exp)
Returns the datetime resulting from adding integer_exp months to date_exp.

add-years
Syntax: add-years (date_exp, integer_exp)
Returns the datetime resulting from adding integer_exp years to date_exp.

age
Syntax: age (date_exp)
Returns age as a month-day-year interval based on date_exp and obtained from subtracting date_exp from today's date (as defined by the today function). WARNING - do not use this value in additional arithmetic operations. It may be used with year-of-ymd-interval,

arctan
Syntax: arctan (numeric_exp)
Returns the arctangent of numeric_exp in radians. The arctangent is the angle whose tangent is numeric_exp.

ascii-code
Syntax: ascii-code (string_exp)
Returns a number representing the ascii code value of the leftmost character of string_exp, e.g. ascii('A') is 65.

C Back to Top

cast-decimal
Syntax: cast_decimal (exp)
Returns the value of the expression cast as a decimal.

cast-float
Syntax: cast_float (exp)
Returns the value of the expression cast as a float.

cast-integer
Syntax: cast_integer (exp)
Returns the value of the expression cast as a integer.

cast-real
Syntax: cast_real (exp)
Returns the value of the expression cast as a real.

ceiling
Syntax: ceiling (numeric_exp)
Returns the smallest integer greater than or equal to numeric_exp.

char
Syntax: char (integer_exp)
Returns the character that has the ASCII code value specified by integer_exp. integer_exp should be between 0 and 255. For example, char(65) has the value 'A'.

char-length
Syntax: char_length (string_exp)
Returns the number of characters in string_exp.

characters-to-integer
Syntax: characters-to-integer (string_exp, integer_exp1, integer_exp2)
Returns the integer representation of up to four characters that starts at position integer_exp1 for integer_exp2 characters. The first character in string_exp is at position 1.

concat
Syntax: concat (string_exp1, string_exp2)
Returns a string that is the result of concatenating string_exp1 to string_exp2.

cos
Syntax: cos (numeric_exp)
Returns the cosine of numeric_exp where numeric_exp is an angle expressed in radians.

D Back to Top

datedaydiff
Syntax=datedaydiff(Starting_date, Ending_date)
Returns the number of days between two datetimes.

datetime-to-date
Syntax: datetime-to-date (datetime_exp)
Returns datetime_exp as a date.

date-to-datetime
Syntax: date-to-datetime (date_exp)
Returns date_exp as a datetime.

date-to-days-from-1900
Syntax: date-to-days-from-1900 (date_exp)
Returns the number of days since Jan. 1, 1900 inclusive, i.e. Jan. 1, 1900 returns 1. The value returned will be negative if date_exp is before 1899-12-31.

date-to-string
Syntax: date-to-string (date_exp)
Returns the date_exp converted to a string of the form yyyy-mm-dd.

day
Syntax: day (date_exp|interval_exp)
Returns the day of the month (1-31) from date_exp. Returns the days field (a signed integer) from interval_exp.

dayname
Syntax: dayname (date_exp)
Returns a character string containing the data source-specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English, or Sonntag through Samstag for a data source that uses German) for the day portion of date_exp.

dayofweek
Syntax: dayofweek (date_exp)
Returns the day of the week in date_exp as an integer in the range 1 - 7, where 1 represents Monday.

dayofyear
Syntax: dayofyear (date_exp)
Returns the day of the year in date_exp as an integer in the range 1 - 366.

day-of-ymd-interval
Syntax: day-of-ymd-interval (ymdinterval_exp)
Returns the number of days (a signed integer 0-30) from ymdinterval_exp.

days-from-1900-to-datetime
Syntax: days-from-1900-to-datetime (integer_exp)
Returns the datetime obtained from converting integer_exp days from Jan. 1, 1900 inclusive to a datetime. Note that days-from-1900-to-datetime(1) returns Jan. 1, 1900.

daysint-ymdint
Syntax: daysint-to-ymdint (date_exp, interval_exp)
Returns the year-month-day interval obtained from converting interval_exp relative to the base date date_exp. WARNING - do not use this value in additional arithmetic operations. It may be used with year-of-ymd-interval, month-of-ymd-interval and day-of-ymd-interval.

days-to-end of month
Syntax: days-to-end-of-month (date_exp)
Returns the number of days to the last day of the month represented by date_exp.

decrypt
Syntax: decrypt (string_exp1, string_exp2)
Returns a string decrypted from the encrypted string_exp1 using string_exp2 as the decryption key. See also the encrypt function.

E Back to Top

encrypt
Syntax: encrypt (string_exp1, string_exp2)
Returns a string encrypted from string_exp1 using string_exp2 as the encryption key. See also the decrypt function.

exp
Syntax: exp (numeric_exp)
Returns e raised to the power of numeric_exp. The constant e is the base of the natural logarithm. See also log.

F Back to Top

first-of-month
Syntax: first-of-month (date_exp)
Returns the datetime obtained from converting date_exp to a date with the same year and month but the day set to one. E.g. first-of-month for Jan. 22/95 is Jan. 1/95.

first-word
Syntax: first-word (string_exp) Returns the first "word" in string_exp.

floor
Syntax: floor (numeric_exp)
Returns the largest integer less than or equal to numeric_exp.

H Back to Top

hour
Syntax: hour (time_exp)
Returns the hour (an integer from 0, which is midnight, to 23, which is 11:00 pm) from time_exp.

I Back to Top

integer-divide
Syntax: integer-divide (integer_exp1, integer_exp2)
Returns the integer obtained by truncating the result of integer_exp1 divided by integer_exp2.

L Back to Top

last-of-month
Syntax: last-of-month (date_exp)
Returns a datetime that is the last day of the month represented by date_exp.

left
Syntax: left (string_exp, integer_exp)
Returns the leftmost integer_exp characters of string_exp.

locate
Syntax: locate (string_exp1, string_exp2 [, start] )
Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search starts at position start (integer_exp, 1 by default) of string_exp2. The first character in a string is at position 1. If string_exp1 is not found then zero is returned.

log
Syntax: log (numeric_exp)
Returns the natural logarithm of numeric_exp.

lower
Syntax: lower (string_exp)
Returns string_exp with all uppercase characters shifted to lowercase.

M Back to Top

make-datetime
Syntax: make-datetime (integer_exp1, integer_exp2, integer_exp3)
Returns a datetime constructed from integer_exp1 (the year), integer_exp2 (the month) and integer_exp3 (the day).

minute
Syntax: minute (time_exp)
Returns the minute (an integer from 0-59) from time_exp.

mod
Syntax: mod (integer_exp1, integer_exp2)
Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. integer_exp2 must not be zero (Some databases may accept zero).

month
Syntax: month (date_exp)
Returns the month (an integer from 1-12) from date_exp.

monthname
Syntax: monthname (date_exp)
Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English, or Januar through Dezember for a data source that uses German) for the month portion of date_exp.

month-of-ymd-interval
Syntax: month-of-ymd-interval (ymdinterval_exp)
Returns the number of months (a signed integer 0-11) from ymdinterval_exp.

months-between
Syntax: months-between (date_exp1, date_exp2)
Returns the integer number of months from date_exp1 to date_exp2. If date_exp1 is later than date_exp2 then the result will be a negative number. The days and time portion of the difference are ignored, i.e. the months are not rounded, except if date_exp1 and date_exp2 are the last days of a month.

N Back to Top

now
Syntax: now ()
Returns a datetime value representing the current date and time of the computer that the database software runs on.

number-to-characters
Syntax: number-to-characters (numeric_exp)
Returns the character representation of numeric_exp.

number-to-date
Syntax: number-to-date (integer_exp)
Returns the date represented by integer_exp.

number-to-datetime
Syntax: number-to-datetime (integer_exp)
Returns the datetime represented by integer_exp which is an integer value in YYYYMMDD format.

number-to-string
Syntax: number-to-string (numeric_exp)
Returns the string representation of numeric_exp.

number-to-string-padded
Syntax: number-to-string-padded (numeric_exp, integer_exp)
Returns the string representation of numeric_exp rounded to the nearest integer. integer_exp specifies the size of the string that will be returned -- the string will be padded with leading zeros if necessary. If the number is negative a '-' will start the string. Example: number-to-string-padded (-2.6, 3) would return '-03'. Note that integer_exp must be large enough to hold the largest value of numeric_exp (including '-' sign if applicable) that can occur or an error message will be issued.

O Back to Top

octet-length
Syntax: octet_length (string_exp)
Returns the number of bytes in string_exp.

od_length
Syntax: od_length (string_exp)
Returns the number of characters in string_exp, excluding trailing blanks and the string termination character.

od_power
Syntax: power (numeric_exp, integer_exp)
Returns the value of numeric_exp raised to the power of integer_exp.

od_truncate
Returns numeric_exp truncated to integer_exp places of the decimal point. If integer_exp is negative then numeric_exp is truncated to the absolute value of integer_exp places to the left of the decimal point.

P Back to Top

pack
Syntax: pack (string_exp)
Returns string_exp with leading spaces removed, and all spaces between words reduced to a single space. It also removes leading and trailing commas and semicolons, and leading spaces from words that begin with a comma, period, colon, or semicolon. The resulting string will be padded with spaces on the right to maintain its original size.

phdate-to-date
Syntax: phdate-to-date (integer_exp, phdate column)
Returns the date obtained from converting the PowerHouse date phdate (a two-byte integer in YYMMDD format, where the high-order 7 bits are the year, the middle 4 bits the month, and the low-order 5 bits are the day) and adding the century specified by integer_exp. A century is an integer value such as 19 (representing 1900).

position
Syntax: position (string_exp1, string_exp2)
Returns the starting position of string_exp1 in string_exp2. The first character in a string is at position 1.

power
Syntax: power (numeric_exp1, numeric_exp2)
Returns numeric_exp1 raised to the power numeric_exp2. If numeric_exp1 is negative then numeric_exp2 must result in an integer value.

pre50-month-between
Syntax: pre50-months-between (date_exp1, date_exp2)
Returns the integer number of months from date_exp1 to date_exp2. If date_exp1 is later than date_exp2 then the result will be a negative number. The days and time portion of the difference are ignored, i.e. the months are not rounded.
This function implements the pre-5.0 behaviour.

proper
In cases where this function is not available I have created a series of steps using other functions to create proper names from an UPPER CASE Full Name field where seperate First Name and Surname fields are not available

Q Back to Top

quarter
Syntax: quarter (date_exp)
Returns the quarter in date_exp as a number in the range 1 - 4, where 1 represents January 1 through March 31.

R Back to Top

rand
Syntax: rand (integer_exp)
Generates a random number using integer_exp as a seed value.

reverse
Syntax: reverse (string_exp)
Returns string_exp with the order of the characters reversed, for example, reverse ('ABC') is 'CBA'.

right
Syntax: right (string_exp, integer_exp)
Returns the rightmost integer_exp characters of string_exp.

round-down
Syntax: round-down (numeric_exp, integer_exp)
Returns numeric_exp rounded down to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded down to absolute (integer_exp) places to the left of the decimal point, e.g., round-down (127, -1) rounds to 120.

round-near
Syntax: round-near (numeric_exp, integer_exp)
Returns numeric_exp rounded to the nearest value integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to the nearest value absolute (integer_exp) places to the left of the decimal point, e.g., round-near (125, -1) rounds to 130.

round-up
Syntax: round-up (numeric_exp, integer_exp)
Returns numeric_exp rounded up to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded up to absolute (integer_exp) places to the left of the decimal point, e.g., round-up (123, -1) rounds to 130.

round-zero
Syntax: round-zero (numeric_exp, integer_exp)
Returns numeric_exp rounded to the value closest to zero integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to the value closest to zero absolute (integer_exp) places to the left of the decimal point, e.g., round-zero (-129, -1) rounds to -120.

S Back to Top

sign
Syntax: sign (numeric_exp)
Returns an indicator of the sign of numeric_exp: +1 if numeric_exp is positive, 0 if zero or -1 if negative.

sin
Syntax: sin (numeric_exp)
Returns the sine of numeric_exp where numeric_exp is an angle expressed in radians.

sound-of
Syntax: sound-of (string_exp)
Returns a 4 character string code obtained by systematically abbreviating words and names in string_exp according to phonetics. Can be used to determine if two strings sound the same, e.g., does sound-of ('SMITH') = sound-of ('SMYTH').

space
Syntax: space (integer_exp)
Returns a string consisting of integer_exp spaces.

spread
Syntax: spread (string_exp)
Returns string_exp with a space added between each of the original characters, e.g., spread ('ABC') returns 'A B C'.

sqrt
Syntax: sqrt (numeric_exp)
Returns the square root of numeric_exp. numeric_exp must be non-negative.

string-to-integer
Syntax: string-to-integer (string_exp)
Returns the integer representation of numeric string_exp.

string-to-number
Syntax: string-to-number (string_exp)
Converts string_exp to a number. string_exp may contain leading spaces, a sign, the digits 0-9, a decimal point and trailing spaces. All components of string_exp are optional except at least one digit must be specified. Floating point values may also include an exponent of the form "Esd" where s is an optional sign and d is the exponent.

substitute
Syntax: substitute (string_exp1, string_exp2)
Returns string_exp1 with each ^ replaced by the corresponding substring from string_exp2. Substrings in string_exp2 are separated by ^'s, e.g., substitute ('a^d^g', 'bc^efg') returns 'abcdefgg'.

substring
Syntax: substring (string_exp, integer_exp1, integer_exp2)
Returns the substring of string_exp that starts at position integer_exp1 for integer_exp2 characters. The first character in string_exp is at position 1.

T Back to Top

tan
Syntax: tan (numeric_exp)
Returns the tangent of numeric_exp where numeric_exp is an angle expressed in radians.

time-to-zero
Syntax: time-to-zero (datetime_exp)
Returns datetime_exp with the time portion set to zero.

today
Syntax: today
Returns the current date according to the clock of the computer that you are running Impromptu on.

trim-leading
Syntax: trim-leading (string_exp)
Returns string_exp with leading spaces removed.

trim-trailing
Syntax: trim-trailing (string_exp)
Returns string_exp with trailing spaces removed.

U Back to Top

upper
Syntax: upper (string_exp)
Returns string_exp with all lowercase characters shifted to uppercase.

W Back to Top

week
Syntax: week (date_exp)
Returns the week of the year in date_exp as an integer value in the range 1 - 53.

Y Back to Top

year
Syntax: year (date_exp)
Returns the year from date_exp.

year-of-ymd-interval
Syntax: year-of-ymd-interval (ymdinterval_exp)
Returns the number of years (a signed integer) from ymdinterval_exp.

years-between
Syntax: years-between (date_exp1, date_exp2)
Returns the integer number of years from date_exp1 to date_exp2. If date_exp1 is later than date_exp2 then the result will be a negative number. The months, days and time portion of the difference are ignored, i.e. the years are not rounded.

ymdint-between
Syntax: ymdint-between (date_exp1, date_exp2)
Returns the year-month-day interval from date_exp1 to date_exp2. date_exp2 should be later than date_exp1. WARNING - do not use this value in additional arithmetic operations. It may be used with year-of-ymd-interval, month-of-ymd-interval and day-of-ymd-interval.

ymdint-to-daysint
Syntax: ymdint-to-daysint (date_exp, ymdinterval_exp)
Returns the interval obtained from converting ymdinterval_exp relative to the base date date_exp.