+

Search Tips   |   Advanced Search

EJB query: Scalar functions

EJB query contains scalar built-in functions for doing type conversions, string manipulation, and for manipulating date-time values.

Details of EJB query scalar built-in functions follow:


Numeric functions


Type conversion functions

Datetime datatype is converted to its string representation in a format specified by the keyword k. The valid keywords values are ISO, USA, EUR or JIS. If k is not specified the default is ISO.

BIGINT ( < any numeric datatype > ) -> Long
BIGINT ( < string > ) -> Long

The function in the second line of the following code converts the argument to an integer n by truncation, and returns the date that is n-1 days after January 1, 0001:

DATE ( < date string > ) -> Date
DATE (  < any numeric datatype>) -> Date

The following function returns date portion of a timestamp:

The following function converts number to decimal with optional precision p and scale s.

The following function converts string to decimal with optional precision p and scale s.

DOUBLE ( < any numeric datatype > ) -> Double
DOUBLE ( < string > ) -> Double

FLOAT ( < any numeric datatype > ) -> Double
FLOAT ( < string > ) -> Double
Float is a synonym for DOUBLE.

INTEGER ( < any numeric datatype > ) -> Integer
INTEGER ( < string > ) -> Integer

TIME ( < time > ) -> Time
TIME ( < time-string  > ) -> Time
TIME ( < timestamp > ) -> Time
TIME ( < timestamp-string  > ) -> Time

TIMESTAMP ( < timestamp > ) -> Timestamp
TIMESTAMP ( < timestamp-string > ) -> Timestamp


String functions

The following function returns a character string representing absolute value of the argument not including its sign or decimal point. For example, digits( -42.35) is "4235".

The following function returns the length of the argument in bytes. If the argument is a numeric or datetime type, it returns the length of internal representation.

The following function returns a copy of the argument string where all upper case characters have been converted to lower case.

The following function returns the starting position of the first occurrence of argument 1 inside argument 2 with optional start position. If not found, it returns 0.

The following function returns a substring of s beginning at character m and containing n characters. If n is omitted, the substring contains the remainder of string s. The result string is padded with blanks if needed to make a string of length n.

The following function returns a copy of the argument string where all lower case characters have been converted to upper case.


Date - time functions

The following function returns the day portion of its argument. For a duration, the return value can be -99 to 99.

DAY (  Date ) -> Integer
DAY ( < date-string > ) -> Integer
DAY ( < date-duration > ) -> Integer
DAY ( Timestamp  ) -> Integer
DAY ( < timestamp-string > ) -> Integer
DAY ( < timestamp-duration > ) -> Integer

The following function returns one more than number of days from January 1, 0001 to its argument.

DAYS ( Date  ) -> Integer
DAYS ( < Date-string > ) -> Integer
DAYS ( Timestamp  ) -> Integer
DAYS ( < timestamp-string > ) -> Integer

The following function returns the hour part of its argument. For a duration, the return value can be -99 to 99.

HOUR ( Time ) -> Integer
HOUR ( < time-string > ) -> Integer
HOUR ( < time-duration > ) -> Integer
HOUR ( Timestamp ) -> Integer
HOUR ( < timestamp-string > ) -> Integer
HOUR ( < timestamp-duration > ) -> Integer

The following function returns the microsecond part of its argument.

MICROSECOND ( Timestamp ) -> Integer
MICROSECOND ( < timestamp-string > ) -> Integer
MICROSECOND ( < timestamp-duration > ) -> Integer

The following function returns the minute part of its argument. For a duration, the return value can be -99 to 99.

MINUTE ( Time ) -> Integer
MINUTE ( < time-string > ) -> Integer
MINUTE ( < time-duration > ) -> Integer
MINUTE ( Timestamp ) -> Integer
MINUTE ( < timestamp-string > ) -> Integer
MINUTE ( < timestamp-duration > ) -> Integer

The following function returns the month portion of its argument. For a duration, the return value can be -99 to 99.

MONTH (  Date ) -> Integer
MONTH ( < date-string > ) -> Integer
MONTH ( < date-duration > ) -> Integer
MONTH ( Timestamp  ) -> Integer
MONTH ( < timestamp-string > ) -> Integer
MONTH ( < timestamp-duration > ) -> Integer

The following function returns the second part of its argument. For a duration, the return value can be -99 to 99.

SECOND ( Time ) -> Integer
SECOND ( < time-string > ) -> Integer
SECOND ( < time-duration > ) -> Integer
SECOND ( Timestamp ) -> Integer
SECOND ( < timestamp-string > ) -> Integer
SECOND ( < timestamp-duration > ) -> Integer

The following function returns the year portion of its argument. For a duration, the return value can be -9999 to 9999.

YEAR (  Date ) -> Integer
YEAR ( < date-string > ) -> Integer
YEAR ( < date-duration > ) -> Integer
YEAR ( Timestamp  ) -> Integer
YEAR ( < timestamp-string > ) -> Integer
YEAR ( < timestamp-duration > ) -> Integer


Related concepts

  • Scalar functions
  • EJB query language


    Related tasks

  • Use EJB query