Skip to main content
Functions Guide
Updated over 2 weeks ago

Name

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

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

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

Description

Syntax

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)


Name

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, divisor)

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

Description

Syntax

Calculates the average value of numbers

AVG(number)

Combines all text values

CONCAT(text)

Combines unique text values

CONCAT_DISTINCT(text)

Counts the number of values

COUNT(arg)

Counts the distinct number of values

COUNT_DISTINCT(arg)

Calculates the running sum

CUMULATIVE_SUM(number)

Populates missing values for values above or below the row

FILL(arg)

Returns the first-row value

FIRST(arg)

Returns the last-row value

LAG(arg)

Returns the previous-row value

LAST(arg)

Returns the next-row value

LEAD(arg)

Returns the maximum value

MAX(arg)

Returns the median value

MEDIAN(number)

Returns the minimum value

MIN(arg)

Assigns a bucket number based on the number of tiles requested

NTILE(arg)

Assigns a rank to each row with ties receiving the same rank

RANK()

Assigns a row number to each row with ties receiving different values

ROW_NUM()

Calculates the standard deviation of numbers

STDDEV(number)

Calculates the sum of numbers

SUM(number)

Calculates the variance of numbers

VAR(number)


Name

Description

Syntax

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

COALESCE(option1, option2, ,…)

Concatenates multiple text text values into a single text

CONCAT(text1, text2, ‚…)

Determines whether a specified subtext exists within a text

CONTAINS(text, subtext)

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

FIRST_NOT_EMPTY(option1, option2, ...)

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

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

Escapes HTML special characters in text

ESCAPE_HTML(text)

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)

Retrieves the leftmost characters from text

LEFT_PART(text, number)

Computes the length (number of characters) of text

LENGTH(text)

Measures the edit distance (Levenshtein distance) between two texts

LEVENSHTEIN(text1, text2)

Converts text to all lowercase characters

LOWER(text)

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

MID(text, start, [length])

Add padding characters to the left side of text, ensuring it reaches a specified length

PAD_LEFT(text, length, character)

Add padding characters to the right side of text, ensuring it reaches a specified length

PAD_RIGHT(text, length, character)

Capitalizes the first letter of each word in text

PROPER(text)

Extracts text that matches a regular expression pattern

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

Checks if text 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 subtext based on a regular expression pattern

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

Repeats a specified text a given number of times.

REPEAT(text, number)

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

REPLACE(text, search, replace)

Reverses the order of characters in text

REVERSE(text)

Extracts the rightmost characters of text up to a specified length

RIGHT_PART(text, number)

Searches for a specific subtext within 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 text using a delimiter

SPLIT_PART(text, delimiter, part)

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?