Skip to main content

Built-in Functions

SQL Frames provides several built-in functions. It is also possible to create user defined functions.

Number Functions

All the JavaScript Math object static functions are available by default. The function names should be in UPPERCASE. For example, Math.sqrt is simply available as SQRT.

String Functions

FunctionDescription
TOSTRING(x)returns a string representation of the given input value, the semantics is as per JavaScript, ${x}
STARTSWITH(str,x,pos?)checks if a string starts with the given value, optionally starting from a given position
ENDSWITH(str,x,pos?)checks if a string ends with the given value, optionally starting from the given end position
INNDEXOF(str,x,pos?)returns the index of the first occurence of a given value, optionally starting from the given position
CONTAINS(str,x,pos?)checks if a string contains the given value, optionally starting from the given position
SUBSTRING(str,start,end?)returns the substring string from the given position and optionally upto the given end position

Date Functions

The date-fns library is used for most of the date functions.

FunctionDescription
NOW()returns the current date time Date object
TODAY()returns today's Date object
TODATETIME(str,fmt)converts the given string to a Date object parsing using the given format
TODATE(str,fmt)converts the given string to a Date object parsing using the given format, with day granularity
DATEDIFF(part,d1,d2)returns the difference between two dates in the part units
DATEADD(d,num,part)returns a Date object by adding num parts to the given date object
DATETRUNC(d,part)returns a Date object by truncating the give object to the specified part
YEAR(d)returns the year of the given date
MONTH(d)returns the month of the given date, 0-indexed
MONTH1(d)returns the month of the given date, 1-indexed
HOUR(d)returns the hour of the given date time
DAYOFMONTH(d)returns the day of the month of the given date
DAYOFWEEK(d)returns the day of the week of the given date, 0-indexed (this is getDay() of JavaScript Date API)
DAYOFWEEK1(d)returns the day of the week of the given date, 1-indexed (this is getDay()+1 of JavaScript Date API
QUARTER(d)returns the quarter of the given date
WEEKOFYEAR(d)returns the week of the year for the given date (this is local week and not ISO week)
WEEKOFMONTH(d)returns the week of the month for the given date
local week,

Avoid using DAYOFWEEK and DAYOFWEEK1 APIs as they are neither ISO based nor local week based. Even though JavaScript getDay() API is local time based, Sunday is always given 0 while for a local week, different countries have either Sunday or Monday as start of the week.

Following APIs might be provided in the future as needed.

  • ISOWEEKOFYEAR
  • ISODAYOFWEEK