Skip to content
Infoveave Help

Infoveave Query Functions

Infoveave’s Query functions provide business and custom calendar date functions for SQL queries, accepting parameters for Date, Level, and Lag. The Date can be @Start-Date, @End-Date, or @Current-Date in “YYYY-MM-DD” format. Level specifies date granularity (Years, Quarters, Months, Weeks, Days), and Lag is an integer for adjusting the date.

Infoveave offers a set of business and holiday or custom calendar date functions, to be embedded while writing SQL queries for reports or widgets. Infoveave SQL functions accepts three parameters; Date, Level and Lag.

  • Date Date can be of the forms of, @Start-Date, @End-Date, and @Current-Date. The valid date syntax is “2020-06-19”.
  • Level Level decide the date granularity as Years, Quarters, Months, Weeks and Days. All the levels are case insensitive.
  • Lag Normally a Integer number, and the number level must be added to the given date.

Business Dates

Infoveave supports the functions to include a @business start date and @business end date.

@Business Start Date Returns the business start date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax @BusinessStart(Date, Level, Lag)
  • Examples
    • Select @BusinessStart(“2020-06-19”, year, 0) as Date
      • Function will return “2020-01-01” as the start date of the year, if “2020-01-01” is holiday, then function will return next date which is not in the holidays list that is “2020-01-02”.
    • Select @BusinessStart(“2020-06-19”, year, 1) as Date
      • Function will add the lag value 1 to the date passed and will return start date of that year as “2021-01-01”.
    • Select @BusinessStart(“2020-06-19”, year, -1) as Date
      • Function will subtract the lag value 1 to the date passed and will return start date of that year as “2019-01-01”.

@Business End Date Returns the business end date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax @BusinessEnd(Date, Level, Lag)
  • Examples
    • Select @BusinessEnd(“2020-06-19”, year, 0) as Date
      • Function will return “2020-12-31” as the end date of the year, if “2020-12-31” is a holiday, then function will return the previous date which is not in the holidays list that is “2020-12-30”.
    • Select @BusinessEnd(“2020-06-19”, year, 1) as Date
      • Function will add the lag value 1 to the date passed and will return the end date of that year as “2021-12-31”.
    • Select @BusinessEnd(“2020-06-19”, year, -1) as Date
      • Function will subtract the lag value 1 to the date passed and will return the end date of that year as “2019-12-31”.
    • Business end will support additional lag “-0” that will return the previous date of the current date.
      • Select @BusinessEnd(“2020-06-19”, year, -0) as Date
        • Function will return “2020-06-18” as the business end date.

Calendar Dates

Infoveave supports the functions to include a @calendar start date and @calendar end date.

@Calendar Start Date Returns the calendar start date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax @CalendarStart(Date, Level, Lag)
  • Examples
    • Select @CalendarStart(“2020-06-19”, year, 0) as Date
      • Function will return “2020-01-01” as the start date of the year, if “2020-01-01” is holiday, then function will return next date which is not in the holidays list that is “2020-01-02”.
    • Select @CalendarStart(“2020-06-19”, year, 1) as Date
      • Function will add the lag value 1 to the date passed and will return start date of that year as “2021-01-01”.
    • Select @CalendarStart(“2020-06-19”, year, -1) as Date
      • Function will subtract the lag value 1 to the date passed and will return start date of that year as “2019-01-01”.

@Calendar End Date Returns the calendar end date which is not in the holidays list, based on the date, level and lag passed to the function.

  • Syntax @CalendarEnd(Date, Level, Lag)
  • Examples
    • Select @CalendarEnd(“2020-06-19”, year, 0) as Date
      • Function will return “2020-12-31” as the end date of the year, if “2020-12-31” is a holiday, then function will return the previous date which is not in the holidays list that is “2020-12-30”.
    • Select @CalendarEnd(“2020-06-19”, year, 1) as Date
      • Function will add the lag value 1 to the date passed and will return the end date of that year as “2021-12-31”.
    • Select @CalendarEnd(“2020-06-19”, year, -1) as Date
      • Function will subtract the lag value 1 to the date passed and will return the end date of that year as “2019-12-31”.
    • Calendar end will support additional lag “-0” that will return the previous date of the current date.
      • Select @CalendarEnd(“2020-06-19”, year, -0) as Date
        • Function will return “2020-06-18” as the calendar end date.