All Products
Search

This Product

    Quick BI:Date Functions

    Document Center

    Quick BI:Date Functions

    Last Updated:Sep 03, 2025

    Date functions are used in data analytics to process dates and times. You can use them to format dates, calculate differences between dates, obtain current timestamps, add or subtract time units, and more. This topic introduces how to use the date functions in the system built-in functions.

    BI_DATEADD

    BI_DATEADD returns a date after adding a specified time period.

    Syntax

    BI_DATEADD(date, interval, date_part)

    Parameters

    • date: The date to be returned.

    • interval: The specified time interval.

    • date_part: The specified time unit, supporting the following time units (case-insensitive):

      year: year

      quarter: quarter

      month: Month

      week: week

      day: day

      hour: hour

      minute: minute

      Second: second

    Definition

    Returns the date after adding the specified number <interval> to the specified <date_part> of the date.

    Outputs

    Date

    Example

    BI_DATEADD('2020-09-29', 100, 'day' ) = 2021-01-07

    BI_DATEDIFF

    BI_DATEDIFF returns the time difference between two dates.

    Syntax

    BI_DATEDIFF(date1, date2, date_part)

    Parameters

    • date1: The first date to be returned.

    • date2: The second date to be returned.

    • date_part: The time unit to be returned, supporting the following time units (case-insensitive):

      Year: year

      quarter: quarter

      month: Month

      week: week

      day: day

      hour: hour

      minute: minute

      Second: second

    Definition

    Returns the difference between <date1> and <date2>. Expressed in the unit of <date_part>, only complete <date_part> units are counted.

    Outputs

    Integer

    Example

    BI_DATEDIFF('2024-05-22', '2020-09-29', 'year') = 3

    This means there are 3 complete years between September 29, 2020 and May 22, 2024.

    BI_DATETRUNC

    BI_DATETRUNC truncates a date according to a specific level (day, month, quarter, year, etc.) to obtain the date of the first day of the specified level.

    Syntax

    BI_DATETRUNC(date, date_part)

    Parameters

    • date: The date to be returned.

    • date_part: The time unit for truncating the date, supporting the following time units (case-insensitive):

      year: Year

      quarter: quarter

      Month: month

      week: week

      day: day

      hour: hour

      minute: minute

      second: second

    Definition

    Truncates <date> to the precision specified by <date_part>. For example, when truncated to the month level, this function returns the first day of the month.

    Outputs

    Date

    Example

    BI_DATETRUNC('2020-09-29', 'day') = 2020-09-29

    BI_DATETRUNC('2020-09-29', 'quarter') = 2020-07-01

    BI_DAY

    BI_DAY returns the day of a date.

    Syntax

    BI_DAY(date)

    Parameters

    date: The date from which to return the day.

    Definition

    Returns the day of the given <date> as an integer.

    Outputs

    Integer

    Example

    BI_DAY('2020-09-29') = 29

    BI_DAYOFWEEK

    Syntax

    BI_DAYOFWEEK(date, <start>)

    Parameters

    • date: The date from which to return the day of the week.

    • start: Specifies the starting day of the week, default is "Mon", with the following correspondence (case-insensitive):

      Mon: Monday

      Tue: Tuesday

      Wed: Wednesday

      Thu: Thursday

      Fri: Friday

      Sat: Saturday

      Sun: Sunday

    Definition

    Returns which day of the week <date> is.

    The week starts with <start> as the first day. <start> is optional and defaults to "Mon".

    Outputs

    Numeric value

    Example

    BI_DAYOFWEEK("2024-05-22", "Mon") = 3

    BI_DAYOFWEEK("2024-09-29", "Mon") = 7

    BI_MAKEDATE

    BI_MAKEDATE constructs a specific date.

    Syntax

    BI_MAKEDATE(year, month, day)

    Parameters

    • year: The year to construct.

    • month: The month to construct.

    • day: The day to construct.

    Definition

    Returns a date value constructed from the specified <year>, <month>, and <day>.

    Outputs

    Date

    Example

    BI_MAKEDATE(2020, 9, 29) = 2020-09-29

    BI_MONTH

    BI_MONTH returns the month of a date.

    Syntax

    BI_MONTH(date)

    Parameters

    date: The date from which to return the month.

    Definition

    Returns the month of the given <date> as an integer.

    Outputs

    Integer

    Example

    BI_MONTH('2020-09-29') = 9

    BI_NOW

    BI_NOW() returns the current time.

    Syntax

    BI_NOW()

    Definition

    Returns the current time.

    Outputs

    Date

    Example

    BI_NOW() = 2024-05-22 09:29:29

    BI_QUARTER

    BI_QUARTER returns the quarter of a date.

    Syntax

    BI_QUARTER(date)

    Parameters

    date: The date from which to return the quarter.

    Definition

    Returns the quarter of the given <date> as an integer.

    Outputs

    Integer

    Example

    BI_QUARTER('2020-09-29') = 3

    BI_TODAY

    BI_TODAY() returns the current date.

    Syntax

    BI_TODAY()

    Definition

    Returns the current date.

    Outputs

    Date

    Example

    BI_TODAY() = 2024-05-22

    BI_WEEK

    BI_WEEK returns the week of a date.

    Syntax

    BI_WEEK(date)

    Parameters

    date: The date from which to return the week.

    Definition

    Returns the week of the given <date> as an integer.

    Outputs

    Integer

    Example

    BI_WEEK('2010-01-01') = 52

    BI_WEEK('2010-01-06') = 1

    BI_YEAR

    BI_YEAR returns the year of a date.

    Syntax

    BI_YEAR(date)

    Parameters

    date: The date from which to return the year.

    Definition

    Returns the year of the given <date> as an integer.

    Outputs

    Integer

    Example

    BI_YEAR('2020-09-29') = 2020