Oracle Compatibility Functions

    Parent topic: Additional Supplied Modules

    Before using any Oracle Compatibility Functions, run the installation script once for each database. For example, to install the functions in database testdb, use the following command:

    To uninstall Oracle Compatibility Functions, run the uinstall_orafunc.sql script:

    1. $GPHOME/share/postgresql/contrib/uninstall_orafunc.sql

    The following functions are available by default and do not require running the Oracle Compatibility installer:

    Note: The Oracle Compatibility Functions reside in the oracompat schema. To access them, prefix the schema name (oracompat) or alter the database search path to include the schema name. For example:

    1. ALTER DATABASE <db_name> SET <search_path> = $<user>, public, oracompat;

    If you alter the database search path, you must restart the database.

    Oracle and Greenplum Implementation Differences

    There are some differences in the implementation of these compatibility functions in the Greenplum Database from the Oracle implementation. If you use validation scripts, the output may not be exactly the same as in Oracle. Some of the differences are as follows:

    • Oracle performs a decimal round off, Greenplum Database does not:

      • 2.00 becomes 2 in Oracle.
      • 2.0.0 remains 2.00 in Greenplum Database.

    • The provided Oracle Compatibility functions handle implicit type conversions differently. For example, using the decode function:

      1. decode(<expression>, <value>, <return> [,<value>, <return>]...
      2.             [, default])

      Oracle automatically converts expression and each value to the datatype of the first value before comparing. Oracle automatically converts return to the same datatype as the first result.

      The Greenplum implementation restricts return and default to be of the same data type. The expression and value can be different types if the data type of value can be converted into the data type of the expression. This is done implicitly. Otherwise, decode fails with an invalid input syntax error. For example:

      1. SELECT decode('M',true,false);
      2. CASE
      3. ------
      4.  f
      5. (1 row)
      6. SELECT decode(1,'M',true,false);
      7. ERROR: Invalid input syntax for integer:*"M" 
      8. *LINE 1: SELECT decode(1,'M',true,false);

    • Numbers in bigint format are displayed in scientific notation in Oracle, but not in Greenplum Database:

      • 9223372036854775 displays as 9.2234E+15 in Oracle.
      • 9223372036854775 remains 9223372036854775 in Greenplum Database.

    • The default date and timestamp format in Oracle is different than the default format in Greenplum Database. If the following code is executed:

      1. CREATE TABLE TEST(date1 date, time1 timestamp, time2 
      2.                   timestamp with timezone);
      3. INSERT INTO TEST VALUES ('2001-11-11','2001-12-13 
      4.                  01:51:15','2001-12-13 01:51:15 -08:00');
      5. SELECT DECODE(date1, '2001-11-11', '2001-01-01') FROM TEST;

      Greenplum Database returns the row, but Oracle does not return any rows.

      Note: The correct syntax in Oracle to return the row is:

      1. SELECT DECODE(to_char(date1, 'YYYY-MM-DD'), '2001-11-11',

    Oracle Compatibility Functions Reference

    The following are the Oracle Compatibility Functions.

    add_months

    Oracle-compliant function to add a given number of months to a given date.

    1. add_months(<date_expression>, <months_to_add>)

    This Oracle-compatible function adds months_to_add to a date_expression and returns a DATE.

    If the date_expression specifies the last day of the month, or if the resulting month has fewer days than the date_expression, then the returned value is the last day of the resulting month. Otherwise, the returned value has the same day of the month as the date_expression.

    date_expression

    The starting date. This can be any expression that can be implicitly converted to DATE.

    months_to_add

    The number of months to add to the date_expression. This is an integer or any value that can be implicitly converted to an integer. This parameter can be positive or negative.

    1. SELECT name, phone, nextcalldate FROM clientdb
    2. WHERE nextcalldate >= add_months(CURRENT_DATE,6);

    Returns name, phone, and nextcalldate for all records where nextcalldate is at least six months in the future.

    This command is compatible with Oracle syntax and is provided for convenience.

    bitand

    Oracle-compliant function that computes a logical AND operation on the bits of two non-negative values.

    Synopsis

    1. bitand(<expr1>, <expr2>)

    This Oracle-compatible function returns an integer representing an AND operation on the bits of two non-negative values (expr1 and expr2). 1 is returned when the values are the same. 0 is returned when the values are different. Only significant bits are compared. For example, an AND operation on the integers 5 (binary 101) and 1 (binary 001 or 1) compares only the rightmost bit, and results in a value of 1 (binary 1).

    The types of expr1 and expr2 are NUMBER, and the result is of type NUMBER. If either argument is NULL, the result is NULL.

    The arguments must be in the range -(2(n-1)) .. ((2(n-1))-1). If an argument is out of this range, the result is undefined.

    Note:

    • The current implementation of BITAND defines n = 128.
    • PL/SQL supports an overload of BITAND for which the types of the inputs and of the result are all BINARY_INTEGER and for which n = 32.

    expr1

    A non-negative integer expression.

    expr2

    A non-negative integer expression.

    1. SELECT bitand(<expr1>, <expr2>)
    2. FROM ClientDB;

    This command is compatible with Oracle syntax and is provided for convenience.

    concat

    Oracle-compliant function to concatenate two strings together.

    Synopsis

    1. concat (<string1>, <string2>)

    This Oracle-compatible function concatenates two strings (string1 and string2) together.

    The string returned is in the same character set as string1. Its datatype depends on the datatypes of the arguments.

    In concatenations of two different datatypes, the datatype returned is the one that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national datatype, then the returned value is a national datatype. For example:

    1. concat(CLOB, NCLOB) returns NCLOB
    2. concat(NCLOB, NCHAR) returns NCLOB
    3. concat(NCLOB, CHAR) returns NCLOB
    4. concat(NCHAR, CLOB) returns NCLOB

    This function is equivalent to the concatenation operator (||).

    string1/string2

    The two strings to concatenate together.

    Both string1 and string2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

    1. SELECT concat(concat(last_name, '''s job category is '),
    2.      job_id)
    3. FROM employees

    Returns 'Smith's job category is 4B'

    This command is compatible with Oracle syntax and is provided for convenience.

    cosh

    Oracle-compliant function to return the hyperbolic cosine of a given number.

    Synopsis

    1. cosh(<float8>)

    This Oracle-compatible function returns the hyperbolic cosine of the floating 8 input number (float8).

    Note: This function is available by default and can be accessed without running the Oracle Compatibility installer.

    float8

    The input number.

    1. SELECT cosh(0.2)
    2. FROM ClientDB;

    Returns '1.02006675561908'‘ (hyperbolic cosine of 0.2)

    This command is compatible with Oracle syntax and is provided for convenience.

    decode

    Oracle-compliant function to transform a data value to a specified return value. This function is a way to implement a set of CASE statements.

    Note: decode is converted into a reserved word in Greenplum Database. If you want to use the Postgres two-argument decode function that decodes binary strings previously encoded to ASCII-only representation, you must invoke it by using the full schema-qualified syntax, pg_catalog.decode(), or by enclosing the function name in quotes "decode" ().

    Note: Greenplum’s implementation of this function transforms decode into case.

    This results in the following type of output:

    1. gptest=# select decode(a, 1, 'A', 2, 'B', 'C') from 
    2. decodetest;
    3.  case 
    4. ------
    5.  C
    6.  A
    7.  C
    8.  B
    9.  C
    10. (5 rows)

    This also means that if you deparse your view with decode, you will see case expression instead.

    You should use the case function instead of decode.

    Synopsis

    1. decode(<expression>, <value>, <return> [,<value>, <return>]...
    2.        [, default])

    The Oracle-compatible function decode searches for a value in an expression. If the value is found, the function returns the specified value.

    Note: This function is available by default and can be accessed without running the Oracle Compatibility installer.

    expression

    The expression to search.

    value

    The value to find in the expression.

    return

    What to return if expression matches value.

    default

    What to return if expression does not match any of the values.

    Only one expression is passed to the function. Multiple value/return pairs can be passed.

    The default parameter is optional. If default is not specified and if expression does not match any of the passed value parameters, decode returns null. The Greenplum implementation restricts return and to be of the same data type. The expression and value can be different types if the data type of value can be converted into the data type of the expression. This is done implicitly. Otherwise, decode fails with an invalid input syntax error.

    In the following code, decode searches for a value for company_id and returns a specified value for that company. If company_id not one of the listed values, the default value Other is returned.

    1. SELECT decode(company_id, 1, 'EMC',
    2.                           2, 'Greenplum',
    3.                           'Other')
    4. FROM suppliers;

    The following code using CASE statements to produce the same result as the example using decode.

    1. SELECT CASE company_id
    2. WHEN IS NOT DISTINCT FROM 1 THEN 'EMC'
    3. WHEN IS NOT DISTINCT FROM 2 THEN 'Greenplum'
    4. ELSE 'Other'
    5. END
    6. FROM suppliers;

    To assign a range of values to a single return value, either pass an expression for each value in the range, or pass an expression that evaluates identically for all values in the range. For example, if a fiscal year begins on August 1, the quarters are shown in the following table.

    • Method 1 - Include 12 values in the decode function:

      1. SELECT decode(curr_month, 1, 'Q2',
      2.                           2, 'Q3',
      3.                           3, 'Q3',
      4.                           4, 'Q3',
      5.                           5, 'Q4',
      6.                           6, 'Q4',
      7.                           7, 'Q4',
      8.                           8, 'Q1',
      9.                           9, 'Q1',
      10.                          10, 'Q1',
      11.                          11, 'Q2',
      12.                          12, 'Q2')
      13. FROM suppliers;

    • Method 2 - Use an expression that defines a unique value to decode:

      1. SELECT decode((1+MOD(curr_month+4,12)/3)::int, 1, 'Q1',
      2.                                                3, 'Q3',
      3.                                                4, 'Q4',
      4. FROM suppliers;

    This command is compatible with Oracle syntax and is provided for convenience.

    PostgreSQL decode (not compatible with Oracle)

    dump

    Oracle-compliant function that returns a text value that includes the datatype code,the length in bytes, and the internal representation of the expression.

    Synopsis

    1. dump(<expression> [,<integer>])

    This Oracle-compatible function returns a text value that includes the datatype code, the length in bytes, and the internal representation of the expression.

    expression

    Any expression

    integer

    The number of characters to return

    This command is compatible with Oracle syntax and is provided for convenience.

    Oracle-compliant function to return the location of a substring in a string.

    Synopsis

    1. instr(<string>, <substring>, [<position>[,<occurrence>]])

    This Oracle-compatible function searches for a substring in a string. If found, it returns an integer indicating the position of the substring in the string, if not found, the function returns 0.

    Optionally you can specify that the search starts at a given position in the string, and only return the nth occurrence of the substring in the string.

    instrcalculates strings using characters as defined by the input character set.

    The value returned is of NUMBER datatype.

    string

    The string to search.

    substring

    The substring to search for in string.

    Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

    position

    The position is a nonzero integer in string where the search will start. If not specified, this defaults to 1. If this value is negative, the function counts backwards from the end of string then searches towards to beginning from the resulting position.

    occurrence

    Occurrence is an integer indicating which occurrence of the substring should be searched for. The value of occurrence must be positive.

    Both position and occurrence must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer. The default values of both position and occurrence are 1, meaning that the search begins at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters.

    1. SELECT instr('Greenplum', 'e') 
    2. FROM ClientDB;

    Returns 3; the first occurrence of ‘e’

    1. SELECT instr('Greenplum', 'e',1,2)
    2. FROM ClientDB;

    Returns 4; the second occurrence of ‘e’

    This command is compatible with Oracle syntax and is provided for convenience.

    last_day

    Oracle-compliant function to return the last day in a given month.

    Synopsis

    1. last_day(<date_expression>)

    This Oracle-compatible function returns the last day of the month specified by a date_expression.

    The return type is always DATE, regardless of the datatype of date_expression.

    date_expression

    The date value used to calculate the last day of the month. This can be any expression that can be implicitly converted to DATE.

    1. SELECT name, hiredate, last_day(hiredate) "Option Date"
    2. FROM employees;

    Returns the name, hiredate, and last_day of the month of hiredate labeled “ Option Date.”

    This command is compatible with Oracle syntax and is provided for convenience.

    listagg

    Oracle-compliant function that aggregates text values into a string.

    Note: This function is an overloaded function. There are two Oracle-compliant listagg functions, one that takes one argument, the text to be aggregated (see below), and one that takes two arguments, the text to be aggregated and a delimiter (see next page).

    1. listagg(<text>)

    This Oracle-compatible function aggregates text values into a string.

    text

    The text value to be aggregated into a string.

    1. SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t)

    Returns: abcdef

    This command is compatible with Oracle syntax and is provided for convenience.

    listagg (2)

    Oracle-compliant function that aggregates text values into a string, separating each by the separator specified in a second argument.

    Note: This function is an overloaded function. There are two Oracle-compliant listagg functions, one that takes one argument, the text to be aggregated (see previous page), and one that takes two arguments, the text to be aggregated and a delimiter (see below).

    Synopsis

    1. listagg(<text>, <separator>)

    This Oracle-compatible function aggregates text values into a string, separating each by the separator specified in a second argument (separator).

    text

    The text value to be aggregated into a string.

    separator

    The separator by which to delimit the text values.

    1. SELECT oracompat.listagg(t, '.') FROM (VALUES('abc'), 
    2. ('def')) as l(t)

    Returns: abc.def

    This command is compatible with Oracle syntax and is provided for convenience.

    lnnvl

    Oracle-compliant function that returns true if the argument is false or NULL, or false.

    Synopsis

    1. lnnvl(<condition>)

    This Oracle-compatible function takes as an argument a condition and returns true if the condition is false or NULL and false if the condition is true.

    condition

    Any condition that evaluates to true, false, or NULL.

    1. SELECT lnnvl(true)

    Returns: false

    1. SELECT lnnvl(NULL)

    Returns: true

    1. SELECT lnnvl(false)

    Returns: true

    1. SELECT (3=5)

    Returns: true

    This command is compatible with Oracle syntax and is provided for convenience.

    months_between

    Oracle-compliant function to evaluate the number of months between two given dates.

    Synopsis

    1. months_between(<date_expression1>, <date_expression2>)

    This Oracle-compatible function returns the number of months between date_expression1 and date_expression2.

    If date_expression1 is later than date_expression2, then the result is positive.

    If date_expression1 is earlier than date_expression2, then the result is negative.

    If date_expression1 and date_expression2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise the function calculates the fractional portion of the month based on a 31-day month.

    date_expression1, date_expression2

    The date values used to calculate the number of months. This can be any expression that can be implicitly converted to DATE.

    1. SELECT months_between
    2.     (to_date ('2003/07/01', 'yyyy/mm/dd'), 
    3.     to_date ('2003/03/14', 'yyyy/mm/dd'));

    Returns the number of months between July 1, 2003 and March 14, 2014.

    1. SELECT * FROM employees 
    2.     where months_between(hire_date, leave_date) <12;

    Returns the number of months between hire_date and leave_date.

    This command is compatible with Oracle syntax and is provided for convenience.

    nanvl

    Oracle-compliant function to substitute a value for a floating point number when a non-number value is encountered.

    Synopsis

    1. nanvl(<float1>, <float2>)

    This Oracle-compatible function evaluates a floating point number (float1) such as BINARY_FLOAT or BINARY_DOUBLE. If it is a non-number (‘not a number’, NaN), the function returns float2. This function is most commonly used to convert non-number values into either NULL or 0.

    float1

    The or BINARY_NUMBER to evaluate.

    float2

    The value to return if float1 is not a number.

    float1 and float2 can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

    1. SELECT nanvl(binary1, 0)
    2. FROM MyDB;

    Returns 0 if the binary1 field contained a non-number value. Otherwise, it would return the binary1 value.

    This command is compatible with Oracle syntax and is provided for convenience.

    next_day

    Oracle-compliant function to return the date of the next specified weekday after a date.

    This section describes using this function with a string argument; see the following page for details about using this function with an integer argument.

    Note: This function is an overloaded function. There are two Oracle-compliant next_day functions, one that takes a date and a day of the week as its arguments (see below), and one that takes a date and an integer as its arguments (see next page).

    Synopsis

    1. next_day(<date_expression>, `day_of_the_week`)

    This Oracle-compatible function returns the first day_of_the_week (Tuesday, Wednesday, etc.) to occur after a date_expression.

    The weekday must be specified in English.

    The case of the weekday is irrelevant.

    The return type is always DATE, regardless of the datatype of date_expression.

    date_expression

    The starting date. This can be any expression that can be implicitly converted to DATE.

    day_of_the_week

    A string containing the name of a day, in English; for example ‘Tuesday’. day_of_the_week is case-insensitive.

    1. SELECT name, next_day(hiredate,"MONDAY") "Second Week Start"
    2. FROM employees;

    Returns the name and the date of the next Monday after hiredate labeled “Second Week Start“.

    This command is compatible with Oracle syntax and is provided for convenience.

    next_day (2)

    Oracle-compliant function to add a given number of days to a date and returns the date of the following day.

    Note: This function is an overloaded function. There are two Oracle next_day functions, one that takes a date and a day of the week as its arguments (see previous page), and one that takes a date and an integer as its arguments (see below).

    Synopsis

    1. next_day(<date_expression>, <days_to_add>)

    This Oracle-compatible function adds the number of days_to_add to a date_expression and returns the date of the day after the result.

    The return type is always DATE, regardless of the datatype of date_expression.

    The starting date. This can be any expression that can be implicitly converted to DATE.

    days_to_add

    The number of days to be add to the date_expression. This is an integer or any value that can be implicitly converted to an integer. This parameter can be positive or negative.

    Returns the name and the date that is 90 days after hiredate labeled “Benefits Eligibility Date“.

    This command is compatible with Oracle syntax and is provided for convenience.

    Oracle-compliant function that sorts data according to a specific collation.

    Synopsis

    1. nlssort (<variable>, <collation>)

    This Oracle-compatible function sorts data according to a specific collation.

    variable

    The data to sort.

    collation

    The collation type by which to sort.

    1. CREATE TABLE test (name text);
    2. INSERT INTO test VALUES('Anne'), ('anne'), ('Bob'), ('bob');
    3. SELECT * FROM test ORDER BY nlssort(name, 'en_US.UTF-8');
    4.  anne
    5.  Anne
    6.  bob
    7.  Bob
    9. SELECT * FROM test ORDER BY nlssort(name, 'C');
    10.  Anne
    11.  Bob
    12.  anne
    13.  bob

    In the first example, the UTF-8 collation rules are specified. This groups characters together regardless of case.

    In the second example, ASCII (C) collation is specified. This sorts according to ASCII order. The result is that upper case characters are sorted ahead of lower case ones.

    This command is compatible with Oracle syntax and is provided for convenience.

    nvl

    Oracle-compliant function to substitute a specified value when an expression evaluates to null.

    Note: This function is analogous to the PostgreSQL coalesce function.

    1. nvl(<expression_to_evaluate>, <null_replacement_value>)

    This Oracle-compatible function evaluates expression_to_evaluate. If it is null, the function returns null_replacement_value; otherwise, it returns expression_to_evaluate.

    expression_to_evaluate

    The expression to evaluate for a null value.

    null_replacement_value

    The value to return if expression_to_evaluate is null.

    Both expression_to_evaluate and null_replacement_value must be the same data type.

    1. SELECT nvl(contact_name,'None') 
    2. FROM clients;
    3. SELECT nvl(amount_past_due,0) 
    4. FROM txns;
    5. SELECT nvl(nickname, firstname) 
    6. FROM contacts;

    This command is compatible with Oracle syntax and is provided for convenience.

    nvl2

    Oracle-compliant function that returns alternate values for both null and non-null values.

    Synopsis

    1. nvl2(<expression_to_evaluate>, <non_null_replacement_value>,
    2.      <null_replacement_value>)

    This Oracle-compatible function evaluates expression_to_evaluate. If it is not null, the function returns non_null_replacement_value; otherwise, it returns null_replacement_value.

    expression_to_evaluate

    The expression to evaluate for a null value.

    non_null_replacement_value

    The value to return if expression_to_evaluate is not null.

    null_replacement_value

    The value to return if expression_to_evaluate is null.

    1. select nvl2(unit_number,'Multi Unit','Single Unit') 
    2. from clients;

    This command is compatible with Oracle syntax and is provided for convenience.

    oracle.substr

    This Oracle-compliant function extracts a portion of a string.

    Synopsis

    1. oracle.substr(<string>, [<start> [,<char_count>]])

    This Oracle-compatible function extract a portion of a string.

    If start is 0, it is evaluated as 1.

    If start is negative, the starting position is negative, the starting position is start characters moving backwards from the end of string.

    If char_count is not passed to the function, all characters from start to the end of string are returned.

    If char_count is less than 1, null is returned.

    If start or char_count is a number, but not an integer, the values are resolved to integers.

    string

    The string from which to extract.

    start

    An integer specifying the starting position in the string.

    char_count

    An integer specifying the number of characters to extract.

    1. oracle.substr(name,1,15)

    Returns the first 15 characters of name.

    1. oracle.substr("Greenplum",-4,4)

    Returns “plum“.

    1. oracle.substr(name,2)

    Returns all characters of name, beginning with the second character.

    PostgreSQL substr (not compatible with Oracle)

    reverse

    Oracle-compliant function to return the input string in reverse order.

    Synopsis

    1. reverse (<string>)

    This Oracle-compatible function returns the input string (string) in reverse order.

    string

    The input string.

    1. SELECT reverse('gnirts') 
    2. FROM ClientDB;

    Returns 'string'‘

    This command is compatible with Oracle syntax and is provided for convenience.

    round

    Oracle-compliant function to round a date to a specific unit of measure (day, week, etc.).

    Note: This function is an overloaded function. It shares the same name with the Postgres round mathematical function that rounds numeric input to the nearest integer or optionally to the nearest x number of decimal places.

    Synopsis

    1. round (<date_time_expression>, [<unit_of_measure>])

    This Oracle-compatible function rounds a date_time_expression to the nearest unit_of_measure (day, week, etc.). If a unit_of_measure is not specified, the date_time_expression is rounded to the nearest day. It operates according to the rules of the Gregorian calendar.

    If the date_time_expression datatype is TIMESTAMP, the value returned is always of datatype TIMESTAMP.

    If the date_time_expression datatype is DATE, the value returned is always of datatype DATE.

    date_time_expression

    The date to round. This can be any expression that can be implicitly converted to DATE or TIMESTAMP.

    unit_of_measure

    The unit of measure to apply for rounding. If not specified, then the date_time_expression is rounded to the nearest day. Valid parameters are:

    1. SELECT round(TO_DATE('27-OCT-00','DD-MON-YY'), 'YEAR')
    2. FROM ClientDB;

    Returns ‘01-JAN-01’ (27 Oct 00 rounded to the first day of the following year (YEAR))

    1. SELECT round('startdate','Q')
    2. FROM ClientDB;

    Returns ‘01-JUL-92’ (the startdate rounded to the first day of the quarter (Q))

    This command is compatible with Oracle syntax and is provided for convenience.

    PostgreSQL (not compatible with Oracle)

    sinh

    Oracle-compliant function to return the hyperbolic sine of a given number.

    Synopsis

    1. sinh(<float8>)

    This Oracle-compatible function returns the hyperbolic sine of the floating 8 input number (float8).

    Note: This function is available by default and can be accessed without running the Oracle Compatibility installer.

    float8

    The input number.

    1. SELECT sinh(3) 
    2. FROM ClientDB;

    Returns '10.0178749274099'‘(hyperbolic sine of 3)

    This command is compatible with Oracle syntax and is provided for convenience.

    tanh

    Oracle-compliant function to return the hyperbolic tangent of a given number.

    Synopsis

    1. tanh(<float8>)

    This Oracle-compatible function returns the hyperbolic tangent of the floating 8 input number (float8).

    Note:

    This function is available by default and can be accessed without running the Oracle Compatibility installer.

    float8

    The input number.

    1. SELECT tanh(3)
    2. FROM ClientDB;

    Returns '0.99505475368673'‘ (hyperbolic tangent of 3)

    This command is compatible with Oracle syntax and is provided for convenience.

    trunc

    Oracle-compliant function to truncate a date to a specific unit of measure (day, week, hour, etc.).

    Note:

    This function is an overloaded function. It shares the same name with the Postgres trunc and the Oracle trunc mathematical functions. Both of these truncate numeric input to the nearest integer or optionally to the nearest x number of decimal places.

    Synopsis

    1. trunc(<date_time_expression>, [<unit_of_measure>])

    This Oracle-compatible function truncates a date_time_expression to the nearest unit_of_measure (day, week, etc.). If a unit_of_measure is not specified, the date_time_expression is truncated to the nearest day. It operates according to the rules of the Gregorian calendar.

    If the date_time_expression datatype is TIMESTAMP, the value returned is always of datatype TIMESTAMP, truncated to the hour/min level.

    If the date_time_expression datatype is DATE, the value returned is always of datatype DATE.

    date_time_expression

    The date to truncate. This can be any expression that can be implicitly converted to DATE or TIMESTAMP.

    unit_of_measure

    The unit of measure to apply for truncating. If not specified, then date__time_expression is truncated to the nearest day. Valid formats are:

    1. SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
    2. FROM ClientDB;

    Returns ‘01-JAN-92’ (27 Oct 92 truncated to the first day of the year (YEAR))

    1. FROM ClientDB;

    Returns ‘1992-07-01’ (the startdate truncated to the first day of the quarter (), depending on the date_style setting)

    This command is compatible with Oracle syntax and is provided for convenience.