Skip to content
Infoveave Help

Infoveave Query Functions

Infoveave provides a set of Query functions that include business and custom calendar date functions for SQL queries. These functions accept parameters for Date, Level, and Lag. The Date can be @Start-Date, @End-Date, or @Current-Date, and must follow the format “YYYY-MM-DD”. The Level specifies the date granularity, which includes Years, Quarters, Months, Weeks, and Days. The Lag is an integer used to adjust the date.

Infoveave offers a collection of business, holiday, and custom calendar date functions. You can embed these functions while writing SQL queries for reports or widgets. The Infoveave SQL functions accept three parameters: Date, Level, and Lag.

  • Date You can use dates in the form of @Start-Date, @End-Date, or @Current-Date. The valid date format is “2020-06-19”.
  • Level You specify the date granularity using Years, Quarters, Months, Weeks, or Days. These values are not case-sensitive.
  • Lag This is usually an integer. The Lag adds or subtracts the specified number of levels to or from the given date.

Business Dates

Infoveave supports functions for including a @business start date and a @business end date.

@Business Start Date returns the business start date that is not in the holidays list. This is based on the Date, Level, and Lag that you pass to the function.

  • Syntax @BusinessStart(Date, Level, Lag)
  • Examples
  • Select @BusinessStart("2020-06-19", year, 0) as Date
    This function returns “2020-01-01” as the start date of the year. If “2020-01-01” is a holiday, then the function returns the next date that is not in the holidays list, such as “2020-01-02”.
  • Select @BusinessStart("2020-06-19", year, 1) as Date
    This function adds a Lag value of 1 to the date passed and returns the start date of that year as “2021-01-01”.
  • Select @BusinessStart("2020-06-19", year, -1) as Date
    This function subtracts a Lag value of 1 from the date passed and returns the start date of that year as “2019-01-01”.

@Business End Date returns the business end date that is not in the holidays list. This is based on the Date, Level, and Lag that you pass to the function.

  • Syntax @BusinessEnd(Date, Level, Lag)
  • Examples
  • Select @BusinessEnd("2020-06-19", year, 0) as Date
    This function returns “2020-12-31” as the end date of the year. If “2020-12-31” is a holiday, the function returns the previous date that is not in the holidays list, such as “2020-12-30”.
  • Select @BusinessEnd("2020-06-19", year, 1) as Date
    This function adds a Lag value of 1 to the date passed and returns the end date of that year as “2021-12-31”.
  • Select @BusinessEnd("2020-06-19", year, -1) as Date
    This function subtracts a Lag value of 1 from the date passed and returns the end date of that year as “2019-12-31”.
  • The business end function also supports an additional Lag “-0” which returns the previous date of the current date.
  • Select @BusinessEnd("2020-06-19", year, -0) as Date
    This function returns “2020-06-18” as the business end date.

Calendar Dates

Infoveave supports functions for including a @calendar start date and a @calendar end date.

@Calendar Start Date returns the calendar start date that is not in the holidays list. This is based on the Date, Level, and Lag that you pass to the function.

  • Syntax @CalendarStart(Date, Level, Lag)
  • Examples
  • Select @CalendarStart("2020-06-19", year, 0) as Date
    This function returns “2020-01-01” as the start date of the year. If “2020-01-01” is a holiday, the function returns the next date that is not in the holidays list, such as “2020-01-02”.
  • Select @CalendarStart("2020-06-19", year, 1) as Date
    This function adds a Lag value of 1 to the date passed and returns the start date of that year as “2021-01-01”.
  • Select @CalendarStart("2020-06-19", year, -1) as Date
    This function subtracts a Lag value of 1 from the date passed and returns the start date of that year as “2019-01-01”.

@Calendar End Date returns the calendar end date that is not in the holidays list. This is based on the Date, Level, and Lag that you pass to the function.

  • Syntax @CalendarEnd(Date, Level, Lag)
  • Examples
  • Select @CalendarEnd("2020-06-19", year, 0) as Date
    This function returns “2020-12-31” as the end date of the year. If “2020-12-31” is a holiday, the function returns the previous date that is not in the holidays list, such as “2020-12-30”.
  • Select @CalendarEnd("2020-06-19", year, 1) as Date
    This function adds a Lag value of 1 to the date passed and returns the end date of that year as “2021-12-31”.
  • Select @CalendarEnd("2020-06-19", year, -1) as Date
    This function subtracts a Lag value of 1 from the date passed and returns the end date of that year as “2019-12-31”.
  • The calendar end function also supports an additional Lag “-0” which returns the previous date of the current date.
  • Select @CalendarEnd("2020-06-19", year, -0) as Date
    This function returns “2020-06-18” as the calendar end date.