Supported Editors

Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

Get Free Extension

Supported Editors

Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

Get Free Extension

Get Sherloq Free

SQL Functions Library

Explore our SQL Functions Library for a comprehensive list of SQL functions with examples, syntax, and usage tips to enhance your queries.

String Functions

REVERSE()

STUFF()

PATINDEX()

RIGHT()

CHARINDEX()

LEFT()

REPLACE()

LENGTH()/LEN()

TRIM()

LOWER()

SUBSTRING()

UPPER()

CONCAT()

String Functions

REVERSE()

STUFF()

PATINDEX()

RIGHT()

CHARINDEX()

LEFT()

REPLACE()

LENGTH()/LEN()

TRIM()

LOWER()

SUBSTRING()

UPPER()

CONCAT()

String Functions

REVERSE()

STUFF()

PATINDEX()

RIGHT()

CHARINDEX()

LEFT()

REPLACE()

LENGTH()/LEN()

TRIM()

LOWER()

SUBSTRING()

UPPER()

CONCAT()

Numeric Functions

SIGN()

ABS()

RAND()/RANDOM()

MOD()

SQRT()

POWER()

FLOOR()

CEIL()/CEILING()

ROUND()

Numeric Functions

SIGN()

ABS()

RAND()/RANDOM()

MOD()

SQRT()

POWER()

FLOOR()

CEIL()/CEILING()

ROUND()

Numeric Functions

SIGN()

ABS()

RAND()/RANDOM()

MOD()

SQRT()

POWER()

FLOOR()

CEIL()/CEILING()

ROUND()

Aggregate Functions

STDEV()

VARIANCE()

STRING_AGG()

GROUP_CONCAT()

MAX()

MIN()

AVG()

SUM()

COUNT()

Aggregate Functions

STDEV()

VARIANCE()

STRING_AGG()

GROUP_CONCAT()

MAX()

MIN()

AVG()

SUM()

COUNT()

Aggregate Functions

STDEV()

VARIANCE()

STRING_AGG()

GROUP_CONCAT()

MAX()

MIN()

AVG()

SUM()

COUNT()

Date Functions

DATEFROMPARTS()

DATEFORMAT()

EOMONTH()

DAY()

MONTH()

YEAR()

DATEDIFF()

DATEADD()

GETDATE()

Date Functions

DATEFROMPARTS()

DATEFORMAT()

EOMONTH()

DAY()

MONTH()

YEAR()

DATEDIFF()

DATEADD()

GETDATE()

Date Functions

DATEFROMPARTS()

DATEFORMAT()

EOMONTH()

DAY()

MONTH()

YEAR()

DATEDIFF()

DATEADD()

GETDATE()

Window Functions

CAST/CONVERT()

COALESCE()

LAST_VALUE()

FIRST_VALUE()

LEAD()

LAG()

DENSE_RANK()

CASE

RANK()

ROW_NUMBER()

Window Functions

CAST/CONVERT()

COALESCE()

LAST_VALUE()

FIRST_VALUE()

LEAD()

LAG()

DENSE_RANK()

CASE

RANK()

ROW_NUMBER()

Window Functions

CAST/CONVERT()

COALESCE()

LAST_VALUE()

FIRST_VALUE()

LEAD()

LAG()

DENSE_RANK()

CASE

RANK()

ROW_NUMBER()

String Functions

REVERSE()

Returns a string value in a reversed order (characters displayed in reverse order).

REVERSE(string)

SELECT REVERSE('Hello World');
-- Output: 'dlroW olleH'

STUFF()

Deletes a specified length of characters and inserts another string at a specified start position in a string.

STUFF(original_string, start_position, length, insert_string)

SELECT STUFF('Hello World', 1, 5, 'Hi');
-- Output: 'Hi World'

PATINDEX()

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

PATINDEX('%pattern%', string)

SELECT PATINDEX('%world%', 'Hello world');
-- Output: 7

RIGHT()

Extracts a specified number of characters from the end (right side) of a string.

RIGHT(string, number_of_characters)

SELECT RIGHT('Hello World', 5);
-- Output: 'World'

CHARINDEX()

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

CHARINDEX(substring, string [, start_position])
-- Some DBMS use POSITION() or INSTR() instead

SELECT CHARINDEX('World', 'Hello World');
-- Output: 7

LEFT()

Extracts a specified number of characters from the beginning (left side) of a string.

LEFT(string, number_of_characters)

SELECT LEFT('Hello World', 5);
-- Output: 'Hello'

REPLACE()

Replaces all occurrences of a substring within a string with another substring.

REPLACE(original_string, search_string, replacement_string)

SELECT REPLACE('Hello World', 'World', 'SQL');
-- Output: 'Hello SQL'

LENGTH()/LEN()

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

LENGTH(string)  -- Most databases
LEN(string)     -- SQL Server

SELECT LENGTH('Hello World');
-- Output: 11

-- SQL Server version
SELECT LEN('Hello World');
-- Output: 11

TRIM()

Removes leading and/or trailing spaces (or specified characters) from a string.

TRIM([{LEADING | TRAILING | BOTH}] [removal_string] FROM string)
-- or simple form
TRIM(string)

SELECT TRIM('   Hello World   ');
-- Output: 'Hello World'

LOWER()

Converts all characters in a string to lowercase letters.

LOWER(string)

SELECT LOWER('HELLO World');
-- Output: 'hello world'

SUBSTRING()

Extracts a portion of a string based on specified position and length.

SUBSTRING(string FROM start_position [FOR length]) -- or 
SUBSTRING(string, start_position, length)

SELECT SUBSTRING('Hello World', 1, 5);
-- Output: 'Hello'

UPPER()

Converts all characters in a string to uppercase letters.

UPPER(string)

SELECT UPPER('Hello World');
-- Output: 'HELLO WORLD'

CONCAT()

Concatenates two or more strings into a single string.

CONCAT(string1, string2, ..., stringN)

SELECT CONCAT('Hello', ' ', 'World');
-- Output: 'Hello World'

The example demonstrates the usage of the CONCAT function in SQL to join multiple strings, resulting in the output 'Hello World'.

String Functions

REVERSE()

Returns a string value in a reversed order (characters displayed in reverse order).

REVERSE(string)

SELECT REVERSE('Hello World');
-- Output: 'dlroW olleH'

STUFF()

Deletes a specified length of characters and inserts another string at a specified start position in a string.

STUFF(original_string, start_position, length, insert_string)

SELECT STUFF('Hello World', 1, 5, 'Hi');
-- Output: 'Hi World'

PATINDEX()

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

PATINDEX('%pattern%', string)

SELECT PATINDEX('%world%', 'Hello world');
-- Output: 7

RIGHT()

Extracts a specified number of characters from the end (right side) of a string.

RIGHT(string, number_of_characters)

SELECT RIGHT('Hello World', 5);
-- Output: 'World'

CHARINDEX()

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

CHARINDEX(substring, string [, start_position])
-- Some DBMS use POSITION() or INSTR() instead

SELECT CHARINDEX('World', 'Hello World');
-- Output: 7

LEFT()

Extracts a specified number of characters from the beginning (left side) of a string.

LEFT(string, number_of_characters)

SELECT LEFT('Hello World', 5);
-- Output: 'Hello'

REPLACE()

Replaces all occurrences of a substring within a string with another substring.

REPLACE(original_string, search_string, replacement_string)

SELECT REPLACE('Hello World', 'World', 'SQL');
-- Output: 'Hello SQL'

LENGTH()/LEN()

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

LENGTH(string)  -- Most databases
LEN(string)     -- SQL Server

SELECT LENGTH('Hello World');
-- Output: 11

-- SQL Server version
SELECT LEN('Hello World');
-- Output: 11

TRIM()

Removes leading and/or trailing spaces (or specified characters) from a string.

TRIM([{LEADING | TRAILING | BOTH}] [removal_string] FROM string)
-- or simple form
TRIM(string)

SELECT TRIM('   Hello World   ');
-- Output: 'Hello World'

LOWER()

Converts all characters in a string to lowercase letters.

LOWER(string)

SELECT LOWER('HELLO World');
-- Output: 'hello world'

SUBSTRING()

Extracts a portion of a string based on specified position and length.

SUBSTRING(string FROM start_position [FOR length]) -- or 
SUBSTRING(string, start_position, length)

SELECT SUBSTRING('Hello World', 1, 5);
-- Output: 'Hello'

UPPER()

Converts all characters in a string to uppercase letters.

UPPER(string)

SELECT UPPER('Hello World');
-- Output: 'HELLO WORLD'

CONCAT()

Concatenates two or more strings into a single string.

CONCAT(string1, string2, ..., stringN)

SELECT CONCAT('Hello', ' ', 'World');
-- Output: 'Hello World'

The example demonstrates the usage of the CONCAT function in SQL to join multiple strings, resulting in the output 'Hello World'.

String Functions

REVERSE()

Returns a string value in a reversed order (characters displayed in reverse order).

REVERSE(string)

SELECT REVERSE('Hello World');
-- Output: 'dlroW olleH'

STUFF()

Deletes a specified length of characters and inserts another string at a specified start position in a string.

STUFF(original_string, start_position, length, insert_string)

SELECT STUFF('Hello World', 1, 5, 'Hi');
-- Output: 'Hi World'

PATINDEX()

Returns the starting position of the first occurrence of a pattern in a string. Uses pattern matching with wildcards.

PATINDEX('%pattern%', string)

SELECT PATINDEX('%world%', 'Hello world');
-- Output: 7

RIGHT()

Extracts a specified number of characters from the end (right side) of a string.

RIGHT(string, number_of_characters)

SELECT RIGHT('Hello World', 5);
-- Output: 'World'

CHARINDEX()

Returns the starting position of a substring within a string. Position is 1-based (first character is position 1).

CHARINDEX(substring, string [, start_position])
-- Some DBMS use POSITION() or INSTR() instead

SELECT CHARINDEX('World', 'Hello World');
-- Output: 7

LEFT()

Extracts a specified number of characters from the beginning (left side) of a string.

LEFT(string, number_of_characters)

SELECT LEFT('Hello World', 5);
-- Output: 'Hello'

REPLACE()

Replaces all occurrences of a substring within a string with another substring.

REPLACE(original_string, search_string, replacement_string)

SELECT REPLACE('Hello World', 'World', 'SQL');
-- Output: 'Hello SQL'

LENGTH()/LEN()

Returns the number of characters in a string. Note: Function name varies by DBMS (LENGTH in most, LEN in SQL Server).

LENGTH(string)  -- Most databases
LEN(string)     -- SQL Server

SELECT LENGTH('Hello World');
-- Output: 11

-- SQL Server version
SELECT LEN('Hello World');
-- Output: 11

TRIM()

Removes leading and/or trailing spaces (or specified characters) from a string.

TRIM([{LEADING | TRAILING | BOTH}] [removal_string] FROM string)
-- or simple form
TRIM(string)

SELECT TRIM('   Hello World   ');
-- Output: 'Hello World'

LOWER()

Converts all characters in a string to lowercase letters.

LOWER(string)

SELECT LOWER('HELLO World');
-- Output: 'hello world'

SUBSTRING()

Extracts a portion of a string based on specified position and length.

SUBSTRING(string FROM start_position [FOR length]) -- or 
SUBSTRING(string, start_position, length)

SELECT SUBSTRING('Hello World', 1, 5);
-- Output: 'Hello'

UPPER()

Converts all characters in a string to uppercase letters.

UPPER(string)

SELECT UPPER('Hello World');
-- Output: 'HELLO WORLD'

CONCAT()

Concatenates two or more strings into a single string.

CONCAT(string1, string2, ..., stringN)

SELECT CONCAT('Hello', ' ', 'World');
-- Output: 'Hello World'

The example demonstrates the usage of the CONCAT function in SQL to join multiple strings, resulting in the output 'Hello World'.

Numeric Functions

SIGN()

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

SIGN(number)

SELECT SIGN(-15.5); 
-- Output: -1

ABS()

Returns the absolute (positive) value of a number by removing the negative sign if present.

ABS(number)

SELECT ABS(-123.45); 
-- Output: 123.45

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

RAND([seed]) or RANDOM()

SELECT RAND(); 
-- Output: 0.7294838393 (random value between 0 and 1)

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

MOD(dividend, divisor) or dividend % divisor

SELECT MOD(17, 5); 
-- Output: 2 (remainder when 17 is divided by 5)

SQRT()

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

SQRT(number)

SELECT SQRT(16);
-- Output: 4

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

POWER(base, exponent) or POW(base, exponent)

SELECT POWER(2, 3); 
-- Output: 8 (2 raised to power 3)

FLOOR()

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

FLOOR(number)

SELECT FLOOR(123.45);
-- Output: 123

CEIL()/CEILING()

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

CEIL(number) or CEILING(number)

SELECT CEIL(123.45); 
-- Output: 124

ROUND()

Rounds a number to a specified number of decimal places or to the nearest integer.

ROUND(number [, decimals])

SELECT ROUND(123.4567, 2); 
-- Output: 123.46

Numeric Functions

SIGN()

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

SIGN(number)

SELECT SIGN(-15.5); 
-- Output: -1

ABS()

Returns the absolute (positive) value of a number by removing the negative sign if present.

ABS(number)

SELECT ABS(-123.45); 
-- Output: 123.45

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

RAND([seed]) or RANDOM()

SELECT RAND(); 
-- Output: 0.7294838393 (random value between 0 and 1)

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

MOD(dividend, divisor) or dividend % divisor

SELECT MOD(17, 5); 
-- Output: 2 (remainder when 17 is divided by 5)

SQRT()

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

SQRT(number)

SELECT SQRT(16);
-- Output: 4

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

POWER(base, exponent) or POW(base, exponent)

SELECT POWER(2, 3); 
-- Output: 8 (2 raised to power 3)

FLOOR()

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

FLOOR(number)

SELECT FLOOR(123.45);
-- Output: 123

CEIL()/CEILING()

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

CEIL(number) or CEILING(number)

SELECT CEIL(123.45); 
-- Output: 124

ROUND()

Rounds a number to a specified number of decimal places or to the nearest integer.

ROUND(number [, decimals])

SELECT ROUND(123.4567, 2); 
-- Output: 123.46

Numeric Functions

SIGN()

Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.

SIGN(number)

SELECT SIGN(-15.5); 
-- Output: -1

ABS()

Returns the absolute (positive) value of a number by removing the negative sign if present.

ABS(number)

SELECT ABS(-123.45); 
-- Output: 123.45

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

RAND([seed]) or RANDOM()

SELECT RAND(); 
-- Output: 0.7294838393 (random value between 0 and 1)

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

MOD(dividend, divisor) or dividend % divisor

SELECT MOD(17, 5); 
-- Output: 2 (remainder when 17 is divided by 5)

SQRT()

Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.

SQRT(number)

SELECT SQRT(16);
-- Output: 4

POWER()

Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.

POWER(base, exponent) or POW(base, exponent)

SELECT POWER(2, 3); 
-- Output: 8 (2 raised to power 3)

FLOOR()

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

FLOOR(number)

SELECT FLOOR(123.45);
-- Output: 123

CEIL()/CEILING()

Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.

CEIL(number) or CEILING(number)

SELECT CEIL(123.45); 
-- Output: 124

ROUND()

Rounds a number to a specified number of decimal places or to the nearest integer.

ROUND(number [, decimals])

SELECT ROUND(123.4567, 2); 
-- Output: 123.46

Aggregate Functions

STDEV()

Calculates the standard deviation (population or sample) of a set of numeric values.

Population Standard Deviation: STDDEV_POP(expression) or STDEVP(expression)
Sample Standard Deviation: STDDEV_SAMP(expression) or STDEV(expression)

SELECT STDEV(salary) FROM employees; 
-- Output: 15652.47 (standard deviation of salaries)

VARIANCE()

Calculates the statistical variance (population or sample) of a set of numeric values.

Population Variance: VAR_POP(expression)
Sample Variance: VAR_SAMP(expression) or VARIANCE(expression)

SELECT VARIANCE(salary) FROM employees; 
-- Output: 245000000 (variance of salaries)

STRING_AGG()

Concatenates values from multiple rows into a single string, with specified delimiter.

STRING_AGG(expression, delimiter) [WITHIN GROUP (ORDER BY expression)]

SELECT department, STRING_AGG(employee_name) FROM employees GROUP BY department; 
-- Output: "Sales: John, Mary, Steve"

GROUP_CONCAT()

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

GROUP_CONCAT([DISTINCT] expression [ORDER BY expression] [SEPARATOR delimiter])

SELECT department, GROUP_CONCAT(employee_name) FROM employees GROUP BY department; 
-- Output: "Sales: John, Mary, Steve"

MAX()

Returns the largest value from a specified column or expression, ignoring NULL values.

MAX(expression)

SELECT MAX(salary) FROM employees; 
-- Output: 120000.00 (highest salary)

MIN()

Returns the smallest value from a specified column or expression, ignoring NULL values.

MIN(expression)

SELECT MIN(salary) FROM employees; 
-- Output: 30000.00 (lowest salary)

AVG()

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

AVG([DISTINCT] expression)

SELECT AVG(salary) FROM employees; 
-- Output: 65000.00 (average salary)

SUM()

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

SUM([DISTINCT] expression)

SELECT SUM(salary) FROM employees;
-- Output: 1250000 (total of all salaries)

COUNT()

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

COUNT(*) -- counts all rows 
COUNT(column) -- counts non-null values 
COUNT(DISTINCT column) -- counts unique non-null values

SELECT COUNT(*) FROM employees; 
-- Output: 100 (total number of rows)

Aggregate Functions

STDEV()

Calculates the standard deviation (population or sample) of a set of numeric values.

Population Standard Deviation: STDDEV_POP(expression) or STDEVP(expression)
Sample Standard Deviation: STDDEV_SAMP(expression) or STDEV(expression)

SELECT STDEV(salary) FROM employees; 
-- Output: 15652.47 (standard deviation of salaries)

VARIANCE()

Calculates the statistical variance (population or sample) of a set of numeric values.

Population Variance: VAR_POP(expression)
Sample Variance: VAR_SAMP(expression) or VARIANCE(expression)

SELECT VARIANCE(salary) FROM employees; 
-- Output: 245000000 (variance of salaries)

STRING_AGG()

Concatenates values from multiple rows into a single string, with specified delimiter.

STRING_AGG(expression, delimiter) [WITHIN GROUP (ORDER BY expression)]

SELECT department, STRING_AGG(employee_name) FROM employees GROUP BY department; 
-- Output: "Sales: John, Mary, Steve"

GROUP_CONCAT()

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

GROUP_CONCAT([DISTINCT] expression [ORDER BY expression] [SEPARATOR delimiter])

SELECT department, GROUP_CONCAT(employee_name) FROM employees GROUP BY department; 
-- Output: "Sales: John, Mary, Steve"

MAX()

Returns the largest value from a specified column or expression, ignoring NULL values.

MAX(expression)

SELECT MAX(salary) FROM employees; 
-- Output: 120000.00 (highest salary)

MIN()

Returns the smallest value from a specified column or expression, ignoring NULL values.

MIN(expression)

SELECT MIN(salary) FROM employees; 
-- Output: 30000.00 (lowest salary)

AVG()

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

AVG([DISTINCT] expression)

SELECT AVG(salary) FROM employees; 
-- Output: 65000.00 (average salary)

SUM()

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

SUM([DISTINCT] expression)

SELECT SUM(salary) FROM employees;
-- Output: 1250000 (total of all salaries)

COUNT()

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

COUNT(*) -- counts all rows 
COUNT(column) -- counts non-null values 
COUNT(DISTINCT column) -- counts unique non-null values

SELECT COUNT(*) FROM employees; 
-- Output: 100 (total number of rows)

Aggregate Functions

STDEV()

Calculates the standard deviation (population or sample) of a set of numeric values.

Population Standard Deviation: STDDEV_POP(expression) or STDEVP(expression)
Sample Standard Deviation: STDDEV_SAMP(expression) or STDEV(expression)

SELECT STDEV(salary) FROM employees; 
-- Output: 15652.47 (standard deviation of salaries)

VARIANCE()

Calculates the statistical variance (population or sample) of a set of numeric values.

Population Variance: VAR_POP(expression)
Sample Variance: VAR_SAMP(expression) or VARIANCE(expression)

SELECT VARIANCE(salary) FROM employees; 
-- Output: 245000000 (variance of salaries)

STRING_AGG()

Concatenates values from multiple rows into a single string, with specified delimiter.

STRING_AGG(expression, delimiter) [WITHIN GROUP (ORDER BY expression)]

SELECT department, STRING_AGG(employee_name) FROM employees GROUP BY department; 
-- Output: "Sales: John, Mary, Steve"

GROUP_CONCAT()

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

GROUP_CONCAT([DISTINCT] expression [ORDER BY expression] [SEPARATOR delimiter])

SELECT department, GROUP_CONCAT(employee_name) FROM employees GROUP BY department; 
-- Output: "Sales: John, Mary, Steve"

MAX()

Returns the largest value from a specified column or expression, ignoring NULL values.

MAX(expression)

SELECT MAX(salary) FROM employees; 
-- Output: 120000.00 (highest salary)

MIN()

Returns the smallest value from a specified column or expression, ignoring NULL values.

MIN(expression)

SELECT MIN(salary) FROM employees; 
-- Output: 30000.00 (lowest salary)

AVG()

Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.

AVG([DISTINCT] expression)

SELECT AVG(salary) FROM employees; 
-- Output: 65000.00 (average salary)

SUM()

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

SUM([DISTINCT] expression)

SELECT SUM(salary) FROM employees;
-- Output: 1250000 (total of all salaries)

COUNT()

Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.

COUNT(*) -- counts all rows 
COUNT(column) -- counts non-null values 
COUNT(DISTINCT column) -- counts unique non-null values

SELECT COUNT(*) FROM employees; 
-- Output: 100 (total number of rows)

Date Functions

DATEFROMPARTS()

Creates a date value from specified year, month, and day integers.

DATEFROMPARTS(year, month, day)

SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'

DATEFORMAT()

Converts a date/timestamp expression into a specified string format.

DATE_FORMAT(date_expression, format_string)

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;
-- Output: '2024-12-26'

EOMONTH()

Returns the last day of the month for a specified date with an optional month offset.

EOMONTH(date_expression [, month_offset])

SELECT EOMONTH(order_date) FROM orders;
-- Output: 2024-12-31 (last day of the month for given date)

DAY()

Extracts the day of the month (1-31) from a date or timestamp expression.

DAY(date_expression) 
-- Some databases use: EXTRACT(DAY FROM date_expression)

SELECT DAY(order_date) FROM orders; 
-- Output: 26 (day extracted from date)

MONTH()

Extracts the month value (1-12) from a date or timestamp expression.

MONTH(date_expression) 
-- Some databases use: EXTRACT(MONTH FROM date_expression)

SELECT MONTH(order_date) FROM orders; 
-- Output: 12 (month extracted from date)

YEAR()

Extracts the year value from a date or timestamp expression.

YEAR(date_expression)
 -- Some databases use: EXTRACT(YEAR FROM date_expression)

SELECT YEAR(order_date) FROM orders; 
-- Output: 2024 (year extracted from date)

DATEDIFF()

Calculates the difference between two dates in specified interval units.

DATEDIFF(interval_type, start_date, end_date) 
-- Some databases use: DATE_DIFF() or end_date - start_date

SELECT DATEDIFF(day, order_date, delivery_date) FROM orders; 
-- Output: 5 (difference of 5 days between dates)

DATEADD()

Adds or subtracts a specified time interval from a date value.

DATEADD(interval_type, interval_value, date_expression) 
-- Some databases use: DATE_ADD() or datetime + INTERVAL

SELECT DATEADD(month, 3, order_date) FROM orders; 
-- Output: '2024-03-26' (adds 3 months to order_date)

GETDATE()

Returns the current date and time of the database server.

GETDATE() 
-- Some databases use: CURRENT_TIMESTAMP or NOW()

SELECT GETDATE(); 
-- Output: '2024-12-26 14:30:45.123'

Date Functions

DATEFROMPARTS()

Creates a date value from specified year, month, and day integers.

DATEFROMPARTS(year, month, day)

SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'

DATEFORMAT()

Converts a date/timestamp expression into a specified string format.

DATE_FORMAT(date_expression, format_string)

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;
-- Output: '2024-12-26'

EOMONTH()

Returns the last day of the month for a specified date with an optional month offset.

EOMONTH(date_expression [, month_offset])

SELECT EOMONTH(order_date) FROM orders;
-- Output: 2024-12-31 (last day of the month for given date)

DAY()

Extracts the day of the month (1-31) from a date or timestamp expression.

DAY(date_expression) 
-- Some databases use: EXTRACT(DAY FROM date_expression)

SELECT DAY(order_date) FROM orders; 
-- Output: 26 (day extracted from date)

MONTH()

Extracts the month value (1-12) from a date or timestamp expression.

MONTH(date_expression) 
-- Some databases use: EXTRACT(MONTH FROM date_expression)

SELECT MONTH(order_date) FROM orders; 
-- Output: 12 (month extracted from date)

YEAR()

Extracts the year value from a date or timestamp expression.

YEAR(date_expression)
 -- Some databases use: EXTRACT(YEAR FROM date_expression)

SELECT YEAR(order_date) FROM orders; 
-- Output: 2024 (year extracted from date)

DATEDIFF()

Calculates the difference between two dates in specified interval units.

DATEDIFF(interval_type, start_date, end_date) 
-- Some databases use: DATE_DIFF() or end_date - start_date

SELECT DATEDIFF(day, order_date, delivery_date) FROM orders; 
-- Output: 5 (difference of 5 days between dates)

DATEADD()

Adds or subtracts a specified time interval from a date value.

DATEADD(interval_type, interval_value, date_expression) 
-- Some databases use: DATE_ADD() or datetime + INTERVAL

SELECT DATEADD(month, 3, order_date) FROM orders; 
-- Output: '2024-03-26' (adds 3 months to order_date)

GETDATE()

Returns the current date and time of the database server.

GETDATE() 
-- Some databases use: CURRENT_TIMESTAMP or NOW()

SELECT GETDATE(); 
-- Output: '2024-12-26 14:30:45.123'

Date Functions

DATEFROMPARTS()

Creates a date value from specified year, month, and day integers.

DATEFROMPARTS(year, month, day)

SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'

DATEFORMAT()

Converts a date/timestamp expression into a specified string format.

DATE_FORMAT(date_expression, format_string)

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;
-- Output: '2024-12-26'

EOMONTH()

Returns the last day of the month for a specified date with an optional month offset.

EOMONTH(date_expression [, month_offset])

SELECT EOMONTH(order_date) FROM orders;
-- Output: 2024-12-31 (last day of the month for given date)

DAY()

Extracts the day of the month (1-31) from a date or timestamp expression.

DAY(date_expression) 
-- Some databases use: EXTRACT(DAY FROM date_expression)

SELECT DAY(order_date) FROM orders; 
-- Output: 26 (day extracted from date)

MONTH()

Extracts the month value (1-12) from a date or timestamp expression.

MONTH(date_expression) 
-- Some databases use: EXTRACT(MONTH FROM date_expression)

SELECT MONTH(order_date) FROM orders; 
-- Output: 12 (month extracted from date)

YEAR()

Extracts the year value from a date or timestamp expression.

YEAR(date_expression)
 -- Some databases use: EXTRACT(YEAR FROM date_expression)

SELECT YEAR(order_date) FROM orders; 
-- Output: 2024 (year extracted from date)

DATEDIFF()

Calculates the difference between two dates in specified interval units.

DATEDIFF(interval_type, start_date, end_date) 
-- Some databases use: DATE_DIFF() or end_date - start_date

SELECT DATEDIFF(day, order_date, delivery_date) FROM orders; 
-- Output: 5 (difference of 5 days between dates)

DATEADD()

Adds or subtracts a specified time interval from a date value.

DATEADD(interval_type, interval_value, date_expression) 
-- Some databases use: DATE_ADD() or datetime + INTERVAL

SELECT DATEADD(month, 3, order_date) FROM orders; 
-- Output: '2024-03-26' (adds 3 months to order_date)

GETDATE()

Returns the current date and time of the database server.

GETDATE() 
-- Some databases use: CURRENT_TIMESTAMP or NOW()

SELECT GETDATE(); 
-- Output: '2024-12-26 14:30:45.123'

Window Functions

DATEFROMPARTS()

Creates a date value from specified year, month, and day integers.

DATEFROMPARTS(year, month, day)

SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'

DATEFORMAT()

Converts a date/timestamp expression into a specified string format.

DATE_FORMAT(date_expression, format_string)

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;
-- Output: '2024-12-26'

EOMONTH()

Returns the last day of the month for a specified date with an optional month offset.

EOMONTH(date_expression [, month_offset])

SELECT EOMONTH(order_date) FROM orders;
-- Output: 2024-12-31 (last day of the month for given date)

DAY()

Extracts the day of the month (1-31) from a date or timestamp expression.

DAY(date_expression) 
-- Some databases use: EXTRACT(DAY FROM date_expression)

SELECT DAY(order_date) FROM orders; 
-- Output: 26 (day extracted from date)

MONTH()

Extracts the month value (1-12) from a date or timestamp expression.

MONTH(date_expression) 
-- Some databases use: EXTRACT(MONTH FROM date_expression)

SELECT MONTH(order_date) FROM orders; 
-- Output: 12 (month extracted from date)

YEAR()

Extracts the year value from a date or timestamp expression.

YEAR(date_expression)
 -- Some databases use: EXTRACT(YEAR FROM date_expression)

SELECT YEAR(order_date) FROM orders; 
-- Output: 2024 (year extracted from date)

DATEDIFF()

Calculates the difference between two dates in specified interval units.

DATEDIFF(interval_type, start_date, end_date) 
-- Some databases use: DATE_DIFF() or end_date - start_date

SELECT DATEDIFF(day, order_date, delivery_date) FROM orders; 
-- Output: 5 (difference of 5 days between dates)

DATEADD()

Adds or subtracts a specified time interval from a date value.

DATEADD(interval_type, interval_value, date_expression) 
-- Some databases use: DATE_ADD() or datetime + INTERVAL

SELECT DATEADD(month, 3, order_date) FROM orders; 
-- Output: '2024-03-26' (adds 3 months to order_date)

GETDATE()

Returns the current date and time of the database server.

GETDATE() 
-- Some databases use: CURRENT_TIMESTAMP or NOW()

SELECT GETDATE(); 
-- Output: '2024-12-26 14:30:45.123'

Window Functions

DATEFROMPARTS()

Creates a date value from specified year, month, and day integers.

DATEFROMPARTS(year, month, day)

SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'

DATEFORMAT()

Converts a date/timestamp expression into a specified string format.

DATE_FORMAT(date_expression, format_string)

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;
-- Output: '2024-12-26'

EOMONTH()

Returns the last day of the month for a specified date with an optional month offset.

EOMONTH(date_expression [, month_offset])

SELECT EOMONTH(order_date) FROM orders;
-- Output: 2024-12-31 (last day of the month for given date)

DAY()

Extracts the day of the month (1-31) from a date or timestamp expression.

DAY(date_expression) 
-- Some databases use: EXTRACT(DAY FROM date_expression)

SELECT DAY(order_date) FROM orders; 
-- Output: 26 (day extracted from date)

MONTH()

Extracts the month value (1-12) from a date or timestamp expression.

MONTH(date_expression) 
-- Some databases use: EXTRACT(MONTH FROM date_expression)

SELECT MONTH(order_date) FROM orders; 
-- Output: 12 (month extracted from date)

YEAR()

Extracts the year value from a date or timestamp expression.

YEAR(date_expression)
 -- Some databases use: EXTRACT(YEAR FROM date_expression)

SELECT YEAR(order_date) FROM orders; 
-- Output: 2024 (year extracted from date)

DATEDIFF()

Calculates the difference between two dates in specified interval units.

DATEDIFF(interval_type, start_date, end_date) 
-- Some databases use: DATE_DIFF() or end_date - start_date

SELECT DATEDIFF(day, order_date, delivery_date) FROM orders; 
-- Output: 5 (difference of 5 days between dates)

DATEADD()

Adds or subtracts a specified time interval from a date value.

DATEADD(interval_type, interval_value, date_expression) 
-- Some databases use: DATE_ADD() or datetime + INTERVAL

SELECT DATEADD(month, 3, order_date) FROM orders; 
-- Output: '2024-03-26' (adds 3 months to order_date)

GETDATE()

Returns the current date and time of the database server.

GETDATE() 
-- Some databases use: CURRENT_TIMESTAMP or NOW()

SELECT GETDATE(); 
-- Output: '2024-12-26 14:30:45.123'

Window Functions

DATEFROMPARTS()

Creates a date value from specified year, month, and day integers.

DATEFROMPARTS(year, month, day)

SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'

DATEFORMAT()

Converts a date/timestamp expression into a specified string format.

DATE_FORMAT(date_expression, format_string)

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;
-- Output: '2024-12-26'

EOMONTH()

Returns the last day of the month for a specified date with an optional month offset.

EOMONTH(date_expression [, month_offset])

SELECT EOMONTH(order_date) FROM orders;
-- Output: 2024-12-31 (last day of the month for given date)

DAY()

Extracts the day of the month (1-31) from a date or timestamp expression.

DAY(date_expression) 
-- Some databases use: EXTRACT(DAY FROM date_expression)

SELECT DAY(order_date) FROM orders; 
-- Output: 26 (day extracted from date)

MONTH()

Extracts the month value (1-12) from a date or timestamp expression.

MONTH(date_expression) 
-- Some databases use: EXTRACT(MONTH FROM date_expression)

SELECT MONTH(order_date) FROM orders; 
-- Output: 12 (month extracted from date)

YEAR()

Extracts the year value from a date or timestamp expression.

YEAR(date_expression)
 -- Some databases use: EXTRACT(YEAR FROM date_expression)

SELECT YEAR(order_date) FROM orders; 
-- Output: 2024 (year extracted from date)

DATEDIFF()

Calculates the difference between two dates in specified interval units.

DATEDIFF(interval_type, start_date, end_date) 
-- Some databases use: DATE_DIFF() or end_date - start_date

SELECT DATEDIFF(day, order_date, delivery_date) FROM orders; 
-- Output: 5 (difference of 5 days between dates)

DATEADD()

Adds or subtracts a specified time interval from a date value.

DATEADD(interval_type, interval_value, date_expression) 
-- Some databases use: DATE_ADD() or datetime + INTERVAL

SELECT DATEADD(month, 3, order_date) FROM orders; 
-- Output: '2024-03-26' (adds 3 months to order_date)

GETDATE()

Returns the current date and time of the database server.

GETDATE() 
-- Some databases use: CURRENT_TIMESTAMP or NOW()

SELECT GETDATE(); 
-- Output: '2024-12-26 14:30:45.123'

One place for all your queries,
directly on your SQL editor

Get Free Extension

Double Click to Update Anything

Double click the image placeholders to add images. Do the same for any text, then tweak styles and publish.

Get Started

Learn More

Book a Demo

Get Sherloq Free