Business Date/Time Functions

This list contains business functions for performing date and time calculations.

_add_seconds
Returns the time or datetime, depending on the format of "time_expression", that results from adding "integer_expression" seconds to "time_expression".
_add_minutes
Returns the time or datetime, depending on the format of "time_expression", that results from adding "integer_expression" minutes to "time_expression".
_add_hours
Returns the time or datetime, depending on the format of "time_expression", that results from adding "integer_expression" hours to "time_expression".
_add_days
Returns the date or datetime, depending on the format of "date_expression", that results from adding "integer_expression" days to "date_expression".
_add_months
Adds "integer_expression" months to "date_expression". If the resulting month has fewer days than the day of month component, then the last day of the resulting month is returned. In all other cases the returned value has the same day of month component as "date_expression".
_add_years
Adds "integer_expression" years to "date_expression". If the "date_expression" is February 29 and resulting year is non leap year, then the resulting day is set to February 28. In all other cases the returned value has the same day and month as "date_expression".
_age
Returns a number that is obtained from subtracting "date_expression" from today's date. The returned value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.
current_date
Returns a date value representing the current date of the computer that the database software runs on.
current_time
Returns a time with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the IBM® Cognos® Analytics server.
current_timestamp
Returns a datetime with time zone value, representing the current time of the computer that runs the database software if the database supports this function. Otherwise, it represents the current time of the server.
_day_of_week
Returns the day of week (1 to 7), where 1 is the first day of the week as indicated by the second parameter (1 to 7, 1 being Monday and 7 being Sunday). Note that in ISO 8601 standard, a week begins with Monday being day 1.
_day_of_year
Returns the day of year (1 to 366) in "date_ expression". Also known as Julian day.
_days_between
Returns a positive or negative number representing the number of days between "date_expression1" and "date_expression2". If "date_expression1" < "date_expression2", then the result will be a negative number.
_days_to_end_of_month
Returns a number representing the number of days remaining in the month represented by "date_expression".
_end_of_day
Returns the end of today as a timestamp.
_first_of_month
Returns a date or datetime, depending on the argument, by converting "date_expression" to a date with the same year and month but with the day set to 1.
_from_unixtime
Returns the unix time specified by an integer expression as a timestamp with time zone.
_hour
Returns the value of the hour field in a date expression.
_last_of_month
Returns a date or datetime, depending on the argument, that is the last day of the month represented by "date_expression".
_make_timestamp
Returns a timestamp constructed from "integer_expression1" (the year), "integer_expression2" (the month), and "integer_expression3" (the day). The time portion defaults to 00:00:00.000 .
_minute
Returns the value of the minute field in a date expression.
_month
Returns the value of the month field in a date expression.
_months_between
Returns a positive or negative integer number representing the number of months between "date_expression1" and "date_expression2". If "date_expression1" is earlier than "date_expression2", then a negative number is returned.
_second
Returns the value of the second field in a date expression.
_shift_timezone
Shifts a timestamp value from one time zone to another time zone. This function honors the Daylight Savings Time when applicable. If the first argument is of type "timestamp", then the second and third arguments represent the "from" and "target" time zones, respectively. If the first argument is of type "timestamp with time zone", then the "from" time zone is already implied in the first argument therefore the second argument represents the "target" time zone. The data type of the first argument will also determine the data type of the return value. The second and third arguments are of type "string" and represent time zone identifiers. A list of these identifiers can be found below. Note: using this function will cause local processing.
_start_of_day
Returns the start of today as a timestamp.
_week_of_year
Returns the number of the week of the year of "date_expression" according to the ISO 8601 standard. Week 1 of the year is the first week of the year to contain a Thursday, which is equivalent to the first week containing January 4th. A week starts on Monday (day 1) and ends on Sunday (day 7).
_timezone_hour
Returns the value of the timezone hour field in the expression. The expression must be of type "timestamp with time zone" or "time with time zone".
_timezone_minute
Returns the value of the timezone minute field in the expression. The expression must be of type "timestamp with time zone" or "time with time zone".
_unix_timestamp
Returns the unix time specified by an integer expression as a timestamp with time zone.
_year
Returns the value of the year field in a date expression.
_years_between
Returns a positive or negative integer number representing the number of years between "date_expression1" and "date_expression2". If "date_expression1" < "date_expression2" then a negative value is returned.
_ymdint_between
Returns a number representing the difference between "date_expression1" and "date_expression2". The returned value has the form YYYYMMDD, where YYYY represents the number of years, MM represents the number of months, and DD represents the number of days.

Parent topic: Using the expression editor