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
| Function | Description |
|---|---|
| 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.
| Function | Description |
|---|---|
| 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 |
| 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 |
| ISOWEEKOFYEAR | returns the iso week of the year |
| ISODAYOFWEEK | returns the iso day of the week |
The following functions are deprecated.
| Function | Description |
|---|---|
| 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 |
deprecated API
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.