Skip to main content
Functions Guide
Updated over a week ago

Name

Short Description

Syntax

Creates a datetime based on the provided year, month, day, hour, minute, and second values

DATETIME(year, month, day, hour, minute, second)

Creates a date based on the provided year, month, and day values

DATE(year, month, day)

Adds a specified offset of time (e.g., seconds, minutes, hours) to a given date or datetime

DATE_ADD(date, offset, \second/minute/hour/day/month/year\")"

Calculates the difference in days, weeks, months, quarters, or years between two dates

DATE_DIFF(End_date, Start_date, “day”/”week”/”month”/”quarter”/”year”)

Calculates the number of days in the month for a given date or datetime

DAYS_IN_MONTH(date)

Calculates the number of days in a year for a given date or datetime, considering leap years

DAYS_IN_YEAR(date)

Retrieves the day of the month from a given date or datetime

DAY(date)

Determines the day of the week for a given date or datetime, where Monday is 1, Tuesday is 2, and so on

DAY_OF_WEEK(date)

Calculates the day of the year for a given date or datetime

DAY_OF_YEAR(date)

Extracts the hour component from a given datetime

HOUR(datetime)

Adds a specified number of days, weeks, months, quarters, or years to a date

date + INTERVAL number DAY/MONTH/YEAR

Adds a specified number of seconds, minutes, or hours to a datetime

datetime + INTERVAL number SECOND/MINUTE/HOUR

Extracts the minute component from a given datetime

MINUTE(datetime)

Retrieves the month component from a given date

MONTH(date)

Retrieves the current date and time

NOW()

Retrieves the quarter of the year from a given date or datetime

QUARTER(date)

Extracts the second component from a given datetime

SECOND(datetime)

Returns a standard time period (e.g., day, week, month, quarter, year) for a given date or datetime field

TIME_PERIOD(date, "day"/"week"/"month"/"quarter"/"year")

Calculates the number of time periods between a given date and the current period

TIME_PERIOD_OFFSET(date, "day"/"week"/"month"/"quarter"/"year"/"decade")

Retrieves the current date

TODAY()

Determines the week number of the year for a given date or datetime

WEEK(date)

Retrieves the year component from a given date or datetime

YEAR(date)


Name

Short Description

Syntax

Converts a value to a boolean (true/false) format

TO_BOOLEAN(value)

Converts text to a datetime format

TO_DATETIME(text)

Converts text to a date format

TO_DATE(text)

Converts a value to an integer

TO_INTEGER(value)

Converts a value to a numerical format

TO_NUMBER(value)

Converts a value, whether it's a datetime or any other data type, to a text format

TO_TEXT(value), TO_TEXT(datetime, “[format]”)


Name

Short Description

Syntax

Evaluates conditions and returns a value based on the first true condition

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END

Checks if a value exists within a specified set of values

IN (...,...)


Name

Short Description

Syntax

Returns the absolute value of a number, ignoring its sign (+/-)

ABS(number)

Rounds a number up to the nearest integer

CEIL(number)

Rounds a number down to the nearest integer

FLOOR(number)

Returns the greatest value among the given columns (i.e., arguments)

GREATEST(arg1, arg2, ...)

Returns the smallest value among the given columns (i.e., arguments)

LEAST(arg1, arg2, ...)

Calculates the logarithm of a number to a specified base

LOG(number, base)

Returns the remainder of a division operation

MOD(dividend, diviser)

Raises a number to the power of another number

POWER(number, exponent)

Generates a random decimal value between 0 and 1

RANDOM()

Rounds a number down to the nearest integer

ROUNDDOWN(number)

Rounds a number up to the nearest integer

ROUNDUP(number)

Rounds a number to a specified number of decimal places

ROUND(number, [decimals])


Name

Short Description

Syntax

Returns the first non-null value from a list of options.

COALESCE(option1, option2, ,…)

Returns the first non-empty value from a list of options.

FIRST_NOT_EMPTY(option1, option2, ...)

Returns a value corresponding to the first true condition

IFS(condition1, value1, condition2, value2, ‚…,…)

Returns one value if a condition is true and another if false

IF(condition, value when true, value when false)

Checks if a value or field is empty

IS_EMPTY(arg)

Extracts a specific field from JSON data

JSON_FIELD(json, json_path)

Sets a value for a specific field in JSON data

JSON_SET(json, json_path, value)


Name

Short Description

Syntax

Concatenates multiple text strings into a single string

CONCAT(text1, text2, ‚…)

Computes the hash value of a text using a specified algorithm (MD5 or SHA-1)

HASH(”md5”/”sha1”, text)

Escapes HTML special characters in a text string

ESCAPE_HTML(text)

Retrieves the leftmost characters from a text string

LEFT_SUBSTR(text, number)

Computes the length (number of characters) of a text string

LENGTH(text)

Measures the edit distance (Levenshtein distance) between two text strings

LEVENSHTEIN(text1, text2)

Converts text to all lowercase characters

LOWER(text)

Capitalizes the first letter of each word in a text string

PROPER(text)

Extracts text that matches a regular expression pattern

REGEX_EXTRACT(text, “pattern", [offset])

Checks if a text string matches a specified regular expression pattern

REGEX_MATCH(text, “pattern")

Replaces text that matches a regular expression pattern with a specified replacement

REGEX_REPLACE(text, “pattern”, replacement)

Returns the index of the first character of the first matching substring based on a regular expression pattern

REGEX_SEARCH(text, “pattern", [offset])

Replaces all occurrences of a specified search string with a replacement string

REPLACE(text, search, replace)

Reverses the order of characters in a text string

REVERSE(text)

Extracts the rightmost characters of a text string up to a specified length

RIGHT_SUBSTR(text, number)

Searches for a specific search string within a text and returns the index of the first occurrence

SEARCH(text, search)

Converts text to a SOUNDEX code for phonetic matching

SOUNDEX(text)

Extracts a specific part of a text string using a delimiter

SPLIT_PART(text, delimiter, part)

Extracts a portion of a text string starting from a specified position with a given length

SUBSTRING(text, start, [length])

Converts HTML escape sequences in text back to their original characters

UNESCAPE_HTML(text)

Converts text to all uppercase characters

UPPER(text)

Did this answer your question?