Developer Reference > Data Access Methods > SQL Engine Reference > Scalar Functions
Scalar Functions
Scalar functions are covered in the following topics:
Zen supports scalar functions that may be included in a SQL statement as a primary expression.
Bitwise Operators
Bitwise operators allow you to manipulate the bits of one or more operands. The following are the types of bitwise operators:
Operator
Meaning
&
bitwise AND
~
bitwise NOT
|
bitwise OR
^
bitwise exclusive OR
The storage length of the expression is a key factor to be considered while performing a bitwise operation. The following are the data types supported for bitwise operations:
BIT
TINYINT
SMALLINT
INTEGER
BIGINT
UTINYINT
USMALLINT
UINTEGER
UBIGINT
The following table gives the descriptions, syntax, values returned, and examples of bitwise operators. Each bitwise operator can take only numeric values as its operands.
Bitwise Operator
Description and Syntax
Values Returned
Example
AND
The bitwise AND operator performs a bitwise logical AND operation between two operands. AND compares two bits and assigns a value equal to 1 to the result only if the values of both the bits are equal to 1. Otherwise, the bit in the result is set to 0.
expression & expression
Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation.
In a bitwise AND operation involving operands of different integer data types, the argument of the smaller data type is converted to the larger data type or to the data type that is immediately larger than the larger of the two operands.
If any of the operands involved in a bitwise AND operation is signed, then the resultant value is also signed.
The & operator can be used in conjunction with the IF function to find out whether a table is a system table or a user-defined table.
select Xf$Name, IF(Xf$Flags & 16 = 16, 'System table','User table') from X$File
NOT
The bitwise NOT operator inverts the bit values of any variable and sets the corresponding bit in the result.
~ expression
Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation. The tilde (~) cannot be used as part of a user-defined name.
The bitwise NOT operator returns the reverse of its single operand of the integer data type. All ones are converted to zeros, and all zeros are converted to ones.
The following query performs the complement operation on a numeric literal:
SELECT ~12
The result is -13. The result is negative because the complement operator complements the sign bit also.
OR
The bitwise OR operator performs a bitwise logical OR operation between two operands. OR compares two bits and assigns a value equal to 1 to the result if the values of either or both the bits are equal to 1. If neither bit in the input expressions has a value of 1, the bit in the result is set to 0. The OR operator can take only numeric values as its operands.
expression | expression
Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation.
In a bitwise OR operation involving operands of different integer data types, the argument of the smaller data type is converted to the larger data type or to the data type that is immediately larger than the larger of the two operands.
If any of the operands involved in a bitwise OR operation is signed, then the resultant value will also be signed.
The following could obtain a list of foreign key and primary constraints:
select B.Xf$Name "Table name", C.Xe$Name "Column name",
IF (Xi$Flags & 8192 = 0, 'Primary key', 'Foreign key') "Key type" from X$Index A, X$File B, X$Field C
where (A.Xi$Flags & (16384 | 8192)) > 0 AND A.Xi$File = B.Xf$Id AND A.Xi$Field = C.Xe$Id
OR (exclusive)
The bitwise exclusive OR operator performs a bitwise logical exclusive OR operation between two operands. Exclusive OR compares two bits and assigns a value equal to 0 to the result if the values of both the bits are either 0 or 1. Otherwise, this operator sets the corresponding result bit to 1.
expression ^ expression
Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation. The circumflex (^) cannot be used as part of a user-defined name.
In a bitwise exclusive OR operation involving operands of different integer data types, the argument of the smaller data type is converted to the larger data type or to the data type that is immediately larger than the larger of the two operands.
If any of the operands involved in a bitwise exclusive OR operation is signed, then the resultant value is also signed.
The following SQL query performs the exclusive OR on two numeric literals:
SELECT 12 ^ 8
The result is 4.
 
Truth Table
The following is the truth table for bitwise operations.
A
B
A & B
A | B
A ^ B
~ A
0
0
0
0
0
1
0
1
0
1
1
1
1
0
0
1
1
0
1
1
1
1
0
0
Arithmetic Operators
Date Arithmetic
Zen supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. This is equivalent to executing a convert on the date.
Zen also supports subtracting one date from another to yield a number of days.
Example
SELECT * FROM person P, Class C WHERE p.Date_Of_Birth < ' 1973-09-05' AND c.Start_date >{d '1995-05-08'} + 30
String Functions
String functions are used to process and manipulate columns that consist of text information, such as CHAR, NCHAR, VARCHAR, NVARCHAR, LONGVARCHAR, or NLONGVARCHAR data types.
The string functions support multiple-byte character strings. (Note, however, that CASE (string) does not support multiple-byte character strings. The CASE (string) keyword assumes that the string data is single-byte ASCII. See CASE (string).)
Arguments denoted as string can be the name of column, a string literal, or the result of another scalar function. The following table lists string functions in Zen.
Function
Description
ASCII (string)
Returns a numeric value for the left most character of string. The value is the position of the character in the database code page. See also UNICODE function.
BIT_LENGTH (string)
Returns the length in bits of string
CHAR (code)
Returns a single-character string where the code argument selects the character from the database code page. The argument must be an integer value. See also NCHAR function.
CHAR_LENGTH (string)
Returns the number of bytes in string. All padding is significant for CHAR and NCHAR string.
CHAR_LENGTH2 (string)
Returns the number of characters in string. All padding is significant for CHAR and NCHAR string. A value less than the size of the string may be returned if the data contains double-byte characters.
CHARACTER_LENGTH (string)
Same as CHAR_LENGTH.
CONCAT (string1, string2)
Returns a string that results from combining string1 and string2.
ISNUMERIC(string)
Returns 1 (TRUE) if the string value can be evaluated as a numeric value. Otherwise returns 0 (FALSE).
LCASE or LOWER (string)
Converts all upper case characters in string to lower case.
LEFT (string, count)
Returns the left most count of characters in string. The value of count is an integer.
LENGTH (string)
Returns the number of characters in string. Trailing spaces are counted in a VARCHAR, NVARCHAR, LONGVARCHAR, or NLONGVARCHAR string. Trailing NULLs are counted in a CHAR, NCHAR, LONGVARCHAR, or NLONGVARCHAR string. The string termination character is not counted. When ANSI_PADDING = OFF, trailing NULLs are treated the same as trailing spaces and are not counted in the length of a CHAR column.
LOCATE (string1, string2 [, start ])
Returns the starting position of the first occurrence of string1 within string2. The search within string2 begins at the first character position unless you specify a starting position (start). The search begins at the starting position you specify. The first character position in string2 is 1. The string1 is not found, the function returns the value zero.
LTRIM (string)
Returns the characters of string with leading blanks removed. All padding is significant for CHAR and NCHAR string.
NCHAR (code)
Returns a single-character wide string where the code argument is a Unicode codepoint value. The argument must be an integer value. See also CHAR function.
OCTET_LENGTH (string)
Returns the length of string in octets (bytes). All padding is significant for CHAR and NCHAR string.
POSITION (string1, string2)
Returns the position of string1 in string2. If string1 does not exist in string2, a zero is returned.
REPLACE (string1, string2, string3)
Searches string1 for occurrences of string2 and replaces each with string3. Returns the result. If no occurrences are found, string1 is returned.
REPLICATE (string, count)
Returns a character string composed of string repeated count times. The value of count is an integer.
REVERSE(string)
Returns a character string with the order of the characters reversed. Note that leading spaces in any string types are considered as significant, unlike trailing spaces which are not considered as significant. See Examples for an example.
RIGHT (string, count)
Returns the right most count of characters in string. The value of count is an integer.
RTRIM (string)
Returns the characters of string with trailing blanks removed. When ANSI_PADDING = OFF, trailing NULLs are treated the same as trailing spaces and are removed from a CHAR column value.
SOUNDEX (string)
Converts an alpha string to a four character code to find similar sounding words or names. Returns a four character (SOUNDEX) code to evaluate the similarity of two strings, usually a name.
Note:  Conforms to the current rule set for the official implementation of Soundex used by the United States Government.
SPACE (count)
Returns a character string consisting of count spaces.
STUFF (string1, start, length, string2)
Returns a character string where length characters in string1 beginning at position start have been replaced by string2. The values of start and length are integers.
SUBSTRING (string1, start, length)
Returns a character string derived from string1 beginning at the character position specified by start for length characters. The start value can be any number. The first position of string1 is 1. A start value of 0 or a negative number is considered left of the first position. Length cannot be negative.
UCASE or UPPER (string)
Converts all lower case characters in string to upper case.
UNICODE (string)
Returns the Unicode codepoint value for the left most character of string. See also ASCII function.
Note:  With the exception of CHAR_LENGTH, string functions operate only on strings of up to 65,500 bytes in length. CHAR_LENGTH operates on the full length of strings permitted by the data type in use.
Queries containing a WHERE clause with scalar functions RTRIM or LEFT can be optimized. For example, consider the following query:
SELECT * FROM T1, T2 WHERE T1.C1 = LEFT(T2.C1, 2)
In this case, both sides of the predicate are optimized if T1.C1 and T2.C2 are index columns. The predicate is the complete search condition following the WHERE keyword. Depending on the size of the tables involved in the join, the optimizer chooses the appropriate table to process first.
LTRIM and RIGHT cannot be optimized if they are contained in a complex expression on either side of the predicate.
Examples
The following example creates a new table with an integer and a character column. It inserts 4 rows with values for the character column only, then updates the integer column of those rows with the ASCII character code for each character.
CREATE TABLE numchars(num INTEGER,chr CHAR(1) CASE);
INSERT INTO numchars (chr) VALUES('a');
INSERT INTO numchars (chr) VALUES('b');
INSERT INTO numchars (chr) VALUES('A');
INSERT INTO numchars (chr) VALUES('B');
UPDATE numchars SET num=ASCII(chr);
SELECT * FROM numchars;
Results of SELECT:
num chr
---------- ---
97 a
98 b
65 A
66 B
 
SELECT num FROM numchars WHERE num=ASCII('a')
Results of SELECT:
num
------
97
============ 
The following example concatenates the first and last names in the Person table and results in "RooseveltBora".
SELECT CONCAT(First_name, Last_name) FROM Person WHERE First_name = 'Roosevelt'
============ 
The next example changes the case of the first name to lowercase and then to upper case, and results in "roosevelt", "ROOSEVELT".
SELECT LCASE(First_name),UCASE(First_name) FROM Person WHERE First_name = 'Roosevelt'
============ 
The following example results in first name trimmed to three characters beginning from left, the length as 9 and locate results 0. This query results in "Roo", 9, 0
SELECT LEFT(First_name, 3),LENGTH(First_name), LOCATE(First_name, 'a') FROM Person WHERE First_name = 'Roosevelt'
============ 
The following example illustrates use of LTRIM and RTRIM functions on strings, results in "Roosevelt", "Roosevelt", "elt".
SELECT LTRIM(First_name),RTRIM(First_name), RIGHT(First_name,3) FROM Person WHERE First_name = 'Roosevelt'
============ 
The following examples illustrate use of the SUBSTRING function.
This substring returns up to three characters starting with the second character in the specified column:
SELECT SUBSTRING(First_name,2, 3) FROM Person WHERE First_name = 'Roosevelt'
Results set:
'oos'
This substring returns an empty string because the starting position is beyond the end of the string:
SELECT substring('ABCDE',10,1);
The following substrings return values as specified:
SELECT substring('ABCDE',0,2); – Returns 'A'
SELECT substring('ABCDE',-5,10); – Returns 'ABCD'
SELECT substring('ABCDE',-1,4); – Returns 'AB'
============ 
The following example illustrates use of the SOUNDEX function on strings Smith and Smythe.
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')'
Results set:
S530
S530
============ 
The following example illustrates use of the SOUNDEX function on the Person table finding all last names that sound like "Kennedy".
SELECT Last_Name FROM Person WHERE SOUNDEX(last_name) = SOUNDEX ('Kennedy')
Results of SELECT:
Last_Name
---------
Kandy
Kenady
Kennedy
Kennedy
============ 
The following example illustrates use of the REVERSE function.
SELECT REVERSE(dept_name) from COURSE where dept_name = 'Music'
Results set:
               cisuM
               cisuM
               cisuM
               cisuM
               cisuM
 
5 rows were affected.
Because leading spaces are signficant, the following query returns zero rows:
SELECT * from COURSE WHERE REVERSE(dept_name) = 'cisuM'
This is because dept_name is defined as a CHAR field 20 characters wide. Either of the following query statements returns the expected results:
SELECT * from COURSE WHERE REVERSE(dept_name) = '               cisuM'
 
SELECT * from COURSE WHERE LTRIM(REVERSE(dept_name)) = 'cisuM'
Results set:
MUS 101   Hymnology      3   Music
MUS 102   Church         3   Music
MUS 203   Piano          3   Music
MUS 304   Music Theory   3   Music
MUS 405   Recital        3   Music
 
5 rows were affected.
Numeric Functions
Numeric functions are used to process and manipulate columns that consist of strictly numeric information, such as decimal and integer values. The following table lists numeric functions in Zen.
Function
Description
ABS (numeric_exp)
Returns the absolute (positive) value of numeric_exp.
ACOS (float_exp)
Returns the arc cosine of float_exp as an angle, expressed in radians.
ASIN (float_exp)
Returns the arc sine of float_exp as an angle, expressed in radians.
ATAN (float_exp)
Returns the arc tangent of float_exp as an angle, expressed in radians.
ATAN2 (float_exp1, float_exp2)
Returns the arc tangent of the x and y coordinates, specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians.
CEILING (numeric_exp)
Returns the smallest integer greater than or equal to numeric_exp.
COS (float_exp)
Returns the cosine of float_exp, where float_exp is an angle expressed in radians.
COT (float_exp)
Returns the cotangent of float_exp, where float_exp is an angle expressed in radians.
DEGREES (numeric_exp)
Returns the number of degrees corresponding to numeric_exp radians.
EXP (float_exp)
Returns the exponential value of float_exp.
FLOOR (numeric_exp)
Returns the largest integer less than or equal to numeric_exp.
LOG (float_exp)
Returns the natural logarithm of float_exp.
LOG10 (float_exp)
Returns the base 10 logarithm of float_exp.
MOD (integer_exp1, integer_exp2)
Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.
PI()
Returns the constant value Pi as a floating point value.
POWER (numeric_exp, integer_exp)
Returns the value of numeric_exp to the power of integer_exp.
RADIANS (numeric_exp)
Returns the number of radians equivalent to numeric_exp degrees.
RAND (integer_exp)
Returns a random floating-point value using integer_exp as the optional seed value.
ROUND (numeric_exp, integer_exp)
Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| (absolute value of integer_exp) places to the left of the decimal point.
SIGN (numeric_exp)
Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.
SIN (float_exp)
Returns the sine of float_exp, where float_exp is an angle expressed in radians.
SQRT (float_exp)
Returns the square root of float_exp.
TAN (float_exp)
Returns the tangent of float_exp, where float_exp is an angle expressed in radians.
TRUNCATE (numeric_exp, integer_exp)
Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| (absolute value) places to the left of the decimal point.
Examples
The following example lists the modulus of the number and capacity columns in a table named Room.
SELECT Number, Capacity, MOD(Number, Capacity) FROM Room WHERE Building_Name = 'Faske Building' and Type = 'Classroom'
============ 
The following example selects all salaries from a table named Faculty that are evenly divisible by 100.
SELECT Salary FROM Faculty WHERE MOD(Salary, 100) = 0
Time and Date Functions
Time and date functions enable you to generate, process, and manipulate data with time and date data types. This topic covers the use of these functions.
You may use CURTIME(), CURDATE() and NOW() in INSERT statements to insert the current local date, time, and time stamp values. For example:
CREATE TABLE table1 (col1 DATE)
INSERT INTO table1 VALUES (CURDATE())
All time and date functions support a SELECT subquery in an INSERT statement, as shown here:
INSERT INTO t1 (c1, c2) SELECT CURRENT_DATE(), CURRENT_TIME()
Some functions, such as CURDATE(), CURTIME(), and NOW(), also support direct insert, as in:
INSERT INTO t1 (c1) VALUES (CURDATE())
For more examples, see Time and Date Function Examples.
The following table lists time and date functions in Zen.
Note:  The date and time formats listed here may not match the ones used to display values the Text and Grid views in ZenCC. The displayed format is fixed by the application.
Function
Description
CURDATE()
Returns the current local date in the format 'yyyy-mm-dd'. Uses the local clock date by default. If SET TIME ZONE has been called, then the value of CURDATE() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE.
CURRENT_DATE()
Returns the current UTC date in the format 'dd/mm/yyyy'.
CURTIME()
Returns the current local time in the format 'hh:mm:ss'. Uses the local clock time by default. If SET TIME ZONE has been called, then the value of CURTIME() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE.
CURRENT_TIME()
Returns the current UTC time in the format 'hh:mm:ss'.
CURRENT_TIMESTAMP()
Returns the current UTC date and time in the format 'yyyy-mm-dd hh:mm:ss.mmm'.
DATEADD(datepart, interval, date_exp)
Returns a new DATETIME value by adding an interval to a date. For example, a datepart day, an interval of 11, and a date_exp of January 26, 2020 returns February 6, 2020. Datepart specifies the part of the date to which interval is added and must be one of the following values:
• YEAR
• QUARTER
• MONTH
• DAY
• DAYOFYEAR
• WEEK
• HOUR
• MINUTE
• SECOND
• MILLISECOND
Interval specifies a positive or negative integer value used to increment datepart. If interval contains a fractional portion, the fraction part is ignored.
Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME.
DATEDIFF(datepart, start, end)
Returns an integer for the difference between the two dates. The integer is the number of date and time boundaries crossed between the two dates.
For example, table mytest has two columns,col1 and col2, both of which are DATETIME. The value in col1 is 2000-01-01 11:11:11.234 and the value in col2 is 2004-09-11 10:10:10.211. The following SELECT statement returns 56, because that is the difference in months between col1 and col2:
SELECT DATEDIFF(month, col1, col2) as Month_Difference FROM mytest
Datepart specifies the part of the date on which to calculate the difference and must be one of the following values:
• YEAR
• QUARTER
• MONTH
• DAY
• DAYOFYEAR
• WEEK
• HOUR
• MINUTE
• SECOND
• MILLISECOND
Start specifies the beginning date for the difference calculation. Start is an expression that returns a DATETIME value or a Unicode character string in a DATE format.
End specifies the ending date for the difference calculation. End is an expression that returns a DATETIME value or a Unicode character string in a DATE format.
Start is subtracted from end. An error is returned if the return value is outside of the range for integer values. See Data Type Ranges.
DATEFLOOR(timestamp_exp, interval_unit)
Returns the time stamp determined by rounding timestamp_exp down to the nearest boundary of interval_unit. This rounding is done by setting all time stamp fields to the right of interval_unit to their minimum values. For example, if interval_unit is day, then year, month, and day are all retained from the timestamp_exp and hour, minute and second are set to zero.
Valid values for interval_unit are:
• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
DATEFROMPARTS(year, month, day)
Returns a date value for the specified year, month, and day.
NULL is returned if any of the parameters are NULL.
DATENAME(datepart, date_exp)
Returns an English character string (a VARCHAR) that represents the datepart of date_exp. For example, a datepart month returns the name of the month such as January, February, and so forth. A datepart weekday returns the day of the week such as Monday, Tuesday, and so forth.
Datepart specifies the part of the date to return and must be one of the following values:
• YEAR
• QUARTER
• MONTH
• DAY
• DAYOFYEAR
• WEEK
• WEEKDAY
• HOUR
• MINUTE
• SECOND
• MILLISECOND
Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME.
DATEPART(datepart, date_exp)
Returns an integer that represents the datepart of date_exp. For example, a datepart month returns an integer representing the month (January = 1, December = 12). A datepart weekday returns an integer representing the day of the week (Sunday = 1, Saturday = 7).
Datepart is the part of the date to return and must be one of the following values:
• YEAR
• QUARTER
• MONTH
• DAY
• DAYOFYEAR
• WEEK
• WEEKDAY
• HOUR
• MINUTE
• SECOND
• MILLISECOND
• TZOFFSET
The TZOFFSET value returns a time zone offset in number of minutes (signed). The DATEPART function with TZOFFSET works only with SYSDATETIMEOFFSET() and string literals containing a time zone offset. The time zone offset range is -14:00 through +14:00. See Time and Date Function Examples.
Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME.
DAY(date_exp)
Returns the day of the month for the given date_exp. Identical to DATEPART(day, date_exp). See DATEPART(datepart, date_exp).
DAYNAME(date_exp)
Returns an English character string containing the name of the day (for example, Sunday through Saturday) for the day portion of date_exp.
Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
DAYOFMONTH(date_exp)
Returns the day of the month in date_exp as an integer in the range of 1 to 31. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
DAYOFYEAR(date_exp)
Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds)
Returns a value constructed from the provided parameters. NULL is returned if any of the parameters are NULL.
DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, scale)
Returns a string value for a date and time using specified parameters.
If any of the parameters except scale are NULL, then NULL is returned. If scale is NULL, then an error is returned.
Scale specifies the precision of the fractions value and has a range from 0 to 7. Fractions depends on scale and has a range from 0 to 9999999. For example, if scale is 3, then each fraction represents a millisecond. The number of digits specified for fractions must be less or equal to the value for scale.
The hour_offset specifies the hour portion of a time zone with a range from -14 to +14. The minute_offset specifies the minute portion of a time zone with a range from 0 to 59. Hour_offset and minute_offset must have the same sign unless hour offset is 0.
The default string literal format for DATETIMEOFFSETFROMPARTS is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm].
EVERYN(interval_unit, timestamp_exp, rounding_unit, bucket_size)
Returns the time stamp determined by rounding timestamp_exp down to the beginning of the most recent time bucket, assuming buckets of bucket-size * interval_unit, counting from the beginning of the most recent rounding_unit.
For example, suppose you want to have ten groupings of data every minute. You would need 6-second buckets for this. EVERYN(SECOND, '2022-06-15 08:15:22.891', MINUTE, 6) rounds the given time stamp down to the beginning of the most recent 6-second bucket, counting from the beginning of the most recent MINUTE. The most recent minute is '2022-06-15 08:15:00.000', and the fourth 6-second bucket in this minute begins at '2022-06-15 08:15:18.000', so this is the value returned. The effect is that all time stamps within this bucket receive this same result from EVERYN.
Valid values for both interval_unit and rounding_unit:
• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
Note: EVERYN gives useful results only if rounding_unit is a larger interval than interval_unit.
EXTRACT(extract_field, extract_source)
Returns the extract_field portion of the extract_source. The extract_source argument is a date, time, or interval expression.
The following values are permitted for extract_field and are returned from the target expression:
• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
HOUR(time_exp)
Returns the hour as an integer in the rage of 0 to 23. Time_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
MINUTE(time_exp)
Returns the minute as an integer in the range 0 to 59. Time_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
MONTH(date_exp)
Returns the month as an integer in the range of 1 to 12. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
MONTHNAME(date_exp)
Returns an English character string containing the name of the month (for example, January through December) for the month portion of date_exp. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
NOW()
Returns the current local date and time in the format 'yyyy-mm-dd hh:mm:ss.mmm'.
Uses the local clock time by default. If SET TIME ZONE has been called, then the value of NOW() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE.
QUARTER(date_exp)
Returns the quarter in date_exp as an integer value in the range of 1- 4, where 1 represents January 1 through March 31. Date_exp can bebe a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
SECOND(time_exp)
Returns the second as an integer in the range of 0 to 59. Time_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
SYSDATETIME()
Returns the current local date and time displayed in the format 'yyyy-mm-dd hh:mm:ss.nnnnnnnnn'.
Uses the local clock time by default. Scale is septaseconds on Windows 10, nanoseconds on Linux, and microseconds on all other platforms. Trailing digits not returned are padded with zeros to 9 places. If SET TIME ZONE has been called, then the value of SYSDATETIME() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE.
SYSDATETIMEOFFSET()
Returns the current date and time along with the hour and minute offset between the current time zone and UTC of the computer on which the Zen database engine is running. Daylight saving time (DST) is accounted for.
The default format returned is YYYY-MM-DD hh:mm:ss[.nnnnnnnnn] [<+ | ->hh:mm]. A plus sign indicates that the current time zone is ahead of the UTC. A minus sign indicates that the current time zone is behind the UTC.
SYSUTCDATETIME()
Returns the current local date and time displayed in the format 'yyyy-mm-dd hh:mm:ss.nnnnnnnnn'.
Uses the local clock time by default. Scale is septaseconds on Windows 10, nanoseconds on Linux, and microseconds on all other platforms. Trailing digits not returned are padded with zeros to 9 places. If SET TIME ZONE has been called, then the value of SYSUTCDATETIME() is determined by calculating UTC time and date from the system clock and operating system locale setting.
TIMEFROMPARTS(hour, minute, seconds, fractions, scale)
Returns a time value constructed from the specified time parameters.
If any of the parameters except scale are NULL, then NULL is returned. If scale is NULL, then an error is returned.
Scale specifies the precision of the fractions value and has a range from 0 to 7. Fractions depends on scale and has a range from 0 to 9999999. For example, if scale is 3, then each fraction represents a millisecond. The number of digits specified for fractions must be less or equal to the value for scale.
The default format for TIMEFROMPARTS is hh:mm:ss[.nnnnnnn].
TIMESTAMPADD(interval, integer_exp, timestamp_exp)
Returns the time stamp calculated by adding integer_exp intervals of type interval to timestamp_exp.
The following values are allowed for interval:
• SQL_TSI_YEAR
• SQL_TSI_QUARTER
• SQL_TSI_MONTH
• SQL_TSI_WEEK
• SQL_TSI_DAY
• SQL_TSI_HOUR
• SQL_TSI_MINUTE
• SQL_TSI_SECOND
TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)
Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1.
The values allowed for interval are the same as for TIMESTAMPADD.
WEEK(date_exp)
Returns the week of the year based on the week field in date_exp as an integer in the range of 1 to 53. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
WEEKDAY(date_exp)
Returns the day of the week for the given date_exp, where 1=Sunday and 7=Saturday. Identical to DATEPART(weekday, date_exp). See DATEPART(datepart, date_exp).
YEAR(date_exp)
Returns the year as an integer value. The range depends on the data source. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data.
Time and Date Function Examples
The following example illustrates the use of hour.
SELECT c.Name, c.Credit_Hours FROM Course c WHERE c.Name = ANY (SELECT cl.Name FROM Class cl WHERE c.Name = cl.Name AND c.Credit_Hours >(HOUR (Finish_Time - Start_Time) + 1))
============ 
The following is an example of minute.
SELECT MINUTE(log) FROM billing
============ 
The following example illustrates the use of second.
SELECT SECOND(log) FROM billing;
SELECT log FROM billing WHERE SECOND(log) = 31
============ 
The following example illustrates the use of NOW().
SELECT NOW() - log FROM billing
============ 
The following is a more complex example that uses month, day, year, hour and minute.
SELECT Name, Section, MONTH(Start_Date), DAY(Start_Date), YEAR(Start_Date), HOUR(Start_Time), MINUTE(Start_Time) FROM Class
============ 
The following example illustrates use of CURDATE().
SELECT ID, Name, Section FROM Class WHERE (Start_Date - CURDATE()) <= 2 AND (Start_Date - CURDATE()) >= 0
============ 
The next example gives the day of the month and day of the week of the start date of class from the class table.
SELECT DAYOFMONTH(Start_date), DAYOFWEEK(Start_date) FROM Class;
SELECT * FROM person WHERE YEAR(Date_Of_Birth) < 1970
============ 
The following example illustrates use of DATEPART with the TZoffset parameter.
SELECT DATEPART(TZoffset, SYSDATETIMEOFFSET())
Assuming the statement returns -360, the current time zone is 360 minutes behind UTC.
Assume that SELECT SYSDATETIMEOFFSET() returns 2011-01-24 14:33:08.4650000 -06:00. Given this, the following query returns -360:
SELECT DATEPART(TZoffset, '2011-01-24 14:33:08.4650000 -06:00')
If the time zone portion is omitted from the string literal, 0 is returned:
SELECT DATEPART(TZoffset, '2011-01-24 14:33:08.4650000')
============ 
The following example uses DATEFROMPARTS to return a date from the provided values.
SELECT NOW(), DATEFROMPARTS(DATEPART(Year, NOW()), DATEPART(Month, NOW()), DATEPART(Day, NOW()))
Returns: 2013-05-09 14:33:34.835 PM 5/9/2013
============ 
The following example uses TIMEFROMPARTS to return a time from the provided values.
SELECT NOW(), TIMEFROMPARTS(DATEPART(hour, NOW()), DATEPART(minute, NOW()), DATEPART(second, NOW()), DATEPART(millisecond, NOW()), 3)
Returns: 2013-05-09 15:04:11.425 PM 15:04:11.425
============ 
This example uses DATETIMEFROMPARTS to return a time stamp from the provided values.
SELECT DATETIMEFROMPARTS(1962, 08, 12, 17, 45, 0, 0)
Returns: 1962-08-12 17:45:00.000 PM
============ 
The following example uses DATETIMEOFFSETFROMPARTS to return a string display for the time stamp plus a timezone specification.
SELECT DATETIMEOFFSETFROMPARTS (1962, 08, 12, 17, 45, 0, 0, 5, 0, 0) + ' GMT'
Returns: 1962-08-12 17:45:00 +05:00 GMT
============ 
The following example uses EVERYN to group records in the Billing table into 6-second buckets based on the TIMESTAMP Log field.
SELECT COUNT(*) NumOfPayments, AVG(Amount_Paid) AveragePayment , EVERYN(SECOND,Log,MINUTE,6) TimePeriodStart FROM Billing GROUP BY TimePeriodStart
============ 
This EVERYN example shows how to specify the rounding unit when the bucket size does not divide into it evenly. Suppose for example, every recorded value must be assigned to a 7-second bucket, but you do not want any bucket to cross an HOUR boundary. The following measurements show a series of value recorded around an HOUR boundary:
Time Stamp EVERYN(SECOND, <time stamp>, HOUR, 7)
2022-06-15 08:59:50.891 2022-06-15 08:59:44.000
2022-06-15 08:59:57.891 2022-06-15 08:59:51.000
2022-06-15 08:59:59.891 2022-06-15 08:59:58.000
2022-06-15 09:00:02.891 2022-06-15 09:00:00.000
2022-06-15 09:00:05.891 2022-06-15 09:00:00.000
2022-06-15 09:00:08.891 2022-06-15 09:00:07.000
Note that the final interval of the HOUR, beginning at 2022-06-15 08:59:58.000, is only 2 seconds long because the next interval must start in the next HOUR, namely 2022-06-15 09:00:00.000.
Functionally, EVERYN allows you to parameterize an expression that otherwise would have to be constructed from several other functions and repeated values. For example, this expression using seven function calls:
DATEADD(SECOND,((FLOOR (DATEDIFF(SECOND, DATEFLOOR(ts_val, HOUR), ts_val)/ 8))* 8), DATEFLOOR(ts_val, HOUR))
can be simplified by calling EVERYN:
EVERYN(SECOND, ts_val , HOUR, 8)
System Functions
System functions provide information at a system level.
Function
Description
DATABASE()
Returns the current database name.
NEWID()
Creates a unique value for data type uniqueidentifier
USER()
Returns the login name of the current user.
System Function Examples
The following examples show how to obtain the name of the current user and database:
SELECT USER();
SELECT DATABASE();
============ 
The following example creates a column of data type UNIQUEIDENTIFIER as the first column in new table table1. Setting a default value with the NEWID function provides a unique value for "col1" in each new row within the table.
CREATE TABLE table1 (col1 UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL, col2 INTEGER);
INSERT INTO table1 (col2) VALUES (1);
INSERT INTO table1 (col2) VALUES (2);
INSERT INTO table1 (col2) VALUES (3);
Logical Functions
Logical functions are used to manipulate data based on certain conditions.
Function
Description
COALESCE (expression1, expression2 [, ... ] )
Returns the first non-null argument, starting from the left in the expression list.
See also COALESCE for additional details.
IF (predicate, expression1, expression2)
Returns expression1 if predicate is true. Otherwise returns expression2.
NULL()
Sets a column as NULL values.
IFNULL (exp, value)
If exp is NULL, value is returned. If exp is not null, exp is returned. The possible data type or types of value must be compatible with the data type of exp.
ISNULL (exp, value)
Replaces NULL with the value specified for value. Exp is the expression to check for NULL. Value is the value returned if exp is NULL. Exp is returned if it is not NULL. The data type of value must be compatible with the data type of exp.
NULLIF (exp1, exp2)
NULLIF returns exp1 if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a NULL value.
Logical Function Examples
The COALESCE scalar function takes two or more arguments and returns the first non-null argument, starting from the left in the expression list.
select COALESCE(10, 'abc' + 'def')
Ten is treated as a SMALLINT and ResultType (SMALLINT, VARCHAR) is SMALLINT. Hence, the result type is SMALLINT.
The first parameter is 10, which can be converted to result type SMALLINT. Therefore, the return value of this example is 10.
============ 
The system scalar functions IF and NULL are SQL extensions.
IF allows you to enter different values depending on whether the condition is true or false. For example, if you want to display a column with logical values as "True" or "False" instead of a binary representation, you would use the following SQL statement:
SELECT IF(logicalcol=1, 'True', 'False')
============ 
The system scalar function NULL allows you to set a column as null values. The syntax is:
NULL()
For example, the following SQL statement inserts a row in the Room table with a NULL value for Capacity:
INSERT INTO Room VALUES ('Young Building', 222, NULL(), 'Lab')
============ 
The following example demonstrates how ISNULL returns a value.
CREATE TABLE t8 (c1 INT, c2 CHAR(10));
INSERT INTO t8 VALUES (100, 'string1');
SELECT c1, c2, ISNULL(c1, 1000), ISNULL(C2, 'a string') from t8;
The SELECT returns 100 and string1 because both c1 and c2 contain a value, not a NULL.
INSERT INTO t8 VALUES (NULL, NULL);
SELECT c1, c2, ISNULL(c1, 1000), ISNULL(C2, 'a string') from t8
The SELECT returns 1000 and a string because both c1 and c2 contain a NULL.
============ 
The following statements demonstrate the IFNULL and NULLIF scalar functions. You use these functions when you want to do certain value substitution based on the presence or absence of NULLs and on equality.
CREATE TABLE Demo (col1 CHAR(3));
INSERT INTO Demo VALUES ('abc');
INSERT INTO Demo VALUES (NULL);
INSERT INTO Demo VALUES ('xyz');
Since the second row contains the NULL value, 'foo' is substituted in its place.
SELECT IFNULL(col1, 'foo') FROM Demo
This results in three rows fetched from one column:
"abc"
"foo"
"xyz"
3 rows fetched from 1 column.
The first row contains 'abc,' which matches the second argument of the following NULLIF call.
SELECT NULLIF(col1, 'abc') FROM Demo
A NULL is returned in its place:
<Null>
<Null>
"xyz"
3 rows fetched from 1 column.
Conversion Functions
Conversion functions convert an expression to a particular data type. The CONVERT function is best used when converting between a value and its text representation. The CAST function gives more control over the data type but less control over character formatting. Note that CONVERT supports only a subset of relational types.
CAST converts an expression to a Zen relational data type, provided that the expression can be converted. CAST can convert binary zeros in a string. For example, CAST(c1 AS BINARY(10)), where c1 is a character column that contains binary zeros (nulls).
If both input and output are character strings, output from CAST or CONVERT has the same collation as the input string.
Conversions between CHAR, VARCHAR, or LONGVARCHAR and NCHAR, NVARCHAR, or NLONGVARCHAR assume that CHAR values are encoded using the database code page.
TRY_CAST and TRY_CONVERT are identical to CAST and CONVERT except for handling of data values that cannot be converted. For CAST and CONVERT the entire query fails, but for TRY_CAST and TRY_CONVERT the columns in the query result that fail are filled with nulls. See Conversion Function Examples for the following list of conversion functions in Zen.
Function
Description
CAST (exp AS type)
TRY_CAST (exp AS type)
Converts exp to type, where type may be a data type listed under Zen Supported Data Types, which includes precision and scale parameters.
CONVERT (exp, type [, style ])
TRY_CONVERT (exp, type [, style ])
Converts exp to the type indicated, using the following type arguments:
SQL_BIGINT
SQL_BINARY
SQL_BIT
SQL_CHAR
SQL_DATE
SQL_DECIMAL
SQL_DOUBLE
SQL_FLOAT
SQL_GUID
SQL_INTEGER
SQL_LONGVARBINARY
SQL_LONGVARCHAR
SQL_NUMERIC
SQL_REAL
SQL_SMALLINT
SQL_TIME
SQL_TIMESTAMP
SQL_TINYINT
SQL_VARCHAR
SQL_WCHAR
SQL_WLONGVARCHAR
SQL_WVARCHAR
The CONVERT arguments use SQL_ as a prefix for the data type. The Zen relational data types do not include the SQL_ prefix. Precision and scale take default values.
The optional parameter style applies only to the DATETIME data type. Use of the parameter truncates the milliseconds portion of the DATETIME data type. A style value may be either 20 or 120. Both values specify the canonical format: yyyy-mm-dd hh:mm:ss. See Conversion Function Examples.
Conversion Function Examples
The following example casts a DATE to a CHAR.
CREATE TABLE u1(cdata DATE);
INSERT INTO u1 VALUES(curdate());
SELECT CAST(cdate as (CHAR20)) FROM u1;
If the current date were January 1, 2004, the SELECT returns 2004-01-01.
============ 
The following example converts, respectively, a UBIGINT to a CHAR, and string data to DATE, TIME, and TIMESTAMP.
SELECT CONVERT(id , SQL_CHAR), CONVERT( '1995-06-05', SQL_DATE), CONVERT('10:10:10', SQL_TIME), CONVERT('1990-10-10 10:10:10', SQL_TIMESTAMP) FROM Faculty
============ 
The following example converts a string to DATE then adds 31 to DATE.
SELECT Name FROM Class WHERE Start_date > CONVERT ('1995-05-07', SQL_DATE) + 31
============ 
The following examples show how to cast and convert a UNIQUEIDENTIFIER data type.
CREATE TABLE table1(col1 CHAR(36), col2 UNIQUEIDENTIFIER DEFAULT NEWID());
 
INSERT INTO table1 (col1) VALUES ('1129619D-772C-AAAB-B221-00FF00FF0099');
 
SELECT CAST(col1 AS UNIQUEIDENTIFIER) FROM table1;
 
SELECT CAST(col2 AS LONGVARCHAR) FROM table1;
 
SELECT CONVERT(col2 , SQL_CHAR) FROM table1;
 
SELECT CONVERT('1129619D-772C-AAAB-B221-00FF00FF0099' , SQL_GUID) FROM table1;
============ 
The following examples show how to convert a DATETIME data type with and without the style parameter.
CREATE TABLE table2(col1 DATETIME);
INSERT INTO table2 (col1) VALUES ('2006-12-25 10:10:10.987');
SELECT CONVERT(col1 , SQL_CHAR, 20) FROM table2;
This returns 2006-12-25 10:10:10.
SELECT CONVERT(col1 , SQL_CHAR, 120) FROM table2
This returns 2006-12-25 10:10:10.
SELECT CONVERT(col1 , SQL_CHAR) FROM table2
This returns 2006-12-25 10:10:10.987.
If you want to include the DATETIME milliseconds, omit the style parameter.
Note the following requirements when using the style parameter:
• The type parameter must be SQL_CHAR. Any other data type is ignored.
• The column data type of the expression must be DATETIME.
• The only permissible style values are 20 and 120. Any other value returns an error. The values 20 or 120 specify the canonical format: yyyy-mm-dd hh:mm:ss.
============ 
The following examples show the different results when using CAST and TRY_CAST. The same behavior occurs with CONVERT and TRY_CONVERT.
SELECT CAST ( '10' AS numeric(10,2)); – Success: returns 10.00
SELECT CAST( 'test' AS float ); – Error: returns Expression evaluation error.
SELECT TRY_CAST ( '10' AS numeric(10,2)); – Success: returns 10.00
SELECT TRY_CAST ( 'test' AS float ); – Success: returns NULL
Last modified date: 11/04/2024