All Products
Search

This Product

    Quick BI:Date Functions

    Document Center

    Quick BI:Date Functions

    Last Updated:Feb 09, 2026

    Date functions process dates and times in data analytics. They format dates, calculate differences between dates, return the current timestamp, and add or subtract time units. This topic describes how to use the built-in date functions.

    BI_DATEADD

    BI_DATEADD returns a date after adding a specified time interval.

    Syntax

    BI_DATEADD(date, interval, date_part)

    Parameters

    • date: The date to be returned.

    • interval: Specifies the time interval.

    • date_part: The time unit. Supported values (case-insensitive) are:

      year: year

      quarter: quarter

      Month

      Week

      Day

      hour: hour

      Minute: minute

      second: second

    Definition

    Returns a new date calculated by adding a specified <interval> to the <date_part> of a date.

    Output

    Date

    Example

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

    Limits

    Not supported for Lindorm (LindormTSDB) data sources.

    Supported only for Lindorm (LindormTable, multi-model SQL) data sources version 2.8 and later.

    BI_DATEDIFF

    BI_DATEDIFF returns the difference between two dates.

    Syntax

    BI_DATEDIFF(date1, date2, date_part)

    Parameters

    • date1: The first input date.

    • date2: The second input date.

    • date_part: The time unit for the result. Supported values (case-insensitive) are:

      year: year

      Quarter

      month: month

      Week

      Day

      Hour

      minute: minute

      second: second

    Definition

    Returns the difference between <date1> and <date2>, expressed in full <date_part> units.

    Output

    Integer

    Example

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

    This means there are three full years between September 29, 2020 and May 22, 2024.

    Limits

    Not supported for Lindorm (LindormTSDB) data sources.

    Supported only for Lindorm (LindormTable, multi-model SQL) data sources version 2.8 and later.

    BI_DATETRUNC

    BI_DATETRUNC truncates a date to a specified level, such as day, month, quarter, or year, and returns the first date of that level.

    Syntax

    BI_DATETRUNC(date, date_part)

    Parameters

    • date: The date to return.

    • date_part: The time unit for truncation. Supported values (case-insensitive) are:

      Year

      Quarter

      month: month

      Week: week

      day: day

      Hour

      Minute: minute

      Second

    Definition

    Truncates <date> to the precision of <date_part>. For example, truncating to month returns the first day of that month.

    Output

    Date

    Example

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

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

    Limits

    Not supported for Lindorm data sources.

    BI_DAY

    BI_DAY returns the day of the month from a date.

    Syntax

    BI_DAY(date)

    Parameter description

    date: The date to return.

    Definition

    Returns the day of the month from <date> as an integer.

    Output

    Integer

    Example

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

    BI_DAYOFWEEK

    Syntax

    BI_DAYOFWEEK(date, <start>)

    Parameters

    • date: The input date.

    • start: The first day of the week. Default is "Mon". Supported values (case-insensitive) are:

      Mon: Monday

      Tue: Tuesday

      Wed: Wednesday

      Thu: Thursday

      Fri: Friday

      Sat: Saturday

      Sun: Sunday

    Definition

    Returns the day number of <date> within its week.

    The week starts on <start>. <start> is optional and defaults to "Mon".

    Output

    Number

    Example

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

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

    BI_MAKEDATE

    BI_MAKEDATE constructs a date from year, month, and day values.

    Syntax

    BI_MAKEDATE(year, month, day)

    Parameters

    • year: The year value.

    • month: The month value.

    • day: The day component.

    Definition

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

    Output

    Date

    Example

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

    Limits

    Supported only for Lindorm (LindormTable, multi-model SQL) data sources version 2.8 and later.

    BI_MONTH

    BI_MONTH returns the month from a date.

    Syntax

    BI_MONTH(date)

    Parameter description

    date: The day of the month to return.

    Definition

    Returns the month from <date> as an integer.

    Output

    Integer

    Example

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

    BI_NOW

    BI_NOW returns the current time.

    Syntax

    BI_NOW()

    Definition

    Returns the current time.

    Output

    Date

    Example

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

    BI_QUARTER

    BI_QUARTER returns the quarter from a date.

    Syntax

    BI_QUARTER(date)

    Parameter description

    date: The date that specifies the quarter to return.

    Definition

    Returns the quarter from <date> as an integer.

    Output

    Integer

    Example

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

    BI_TODAY

    BI_TODAY returns the current date.

    Syntax

    BI_TODAY()

    Definition

    Returns the current date.

    Output

    Date

    Example

    BI_TODAY() = 2024-05-22

    BI_WEEK

    BI_WEEK returns the week number from a date.

    Syntax

    BI_WEEK(date)

    Parameter description

    date: The date for the week to return.

    Definition

    Returns the week number from <date> as an integer.

    Output

    Integer

    Example

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

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

    BI_YEAR

    BI_YEAR returns the year from a date.

    Syntax

    BI_YEAR(date)

    Parameter

    date: The date from which to extract the year.

    Definition

    Returns the year from <date> as an integer.

    Output

    Integer

    Example

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