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.
Explore our SQL Functions Library for a comprehensive list of SQL functions with examples, syntax, and usage tips to enhance your queries.
Explore our SQL Functions Library for a comprehensive list of SQL functions with examples, syntax, and usage tips to enhance your queries.
String Functions
Returns a string value in a reversed order (characters displayed in reverse order).
REVERSE(string)
SELECT REVERSE('Hello World');
-- Output: 'dlroW olleH'
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'
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
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'
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
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'
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'
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
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'
Converts all characters in a string to lowercase letters.
LOWER(string)
SELECT LOWER('HELLO World');
-- Output: 'hello world'
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'
String Functions
Returns a string value in a reversed order (characters displayed in reverse order).
REVERSE(string)
SELECT REVERSE('Hello World');
-- Output: 'dlroW olleH'
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'
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
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'
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
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'
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'
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
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'
Converts all characters in a string to lowercase letters.
LOWER(string)
SELECT LOWER('HELLO World');
-- Output: 'hello world'
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'
String Functions
Returns a string value in a reversed order (characters displayed in reverse order).
REVERSE(string)
SELECT REVERSE('Hello World');
-- Output: 'dlroW olleH'
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'
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
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'
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
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'
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'
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
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'
Converts all characters in a string to lowercase letters.
LOWER(string)
SELECT LOWER('HELLO World');
-- Output: 'hello world'
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'
Numeric Functions
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
Returns the absolute (positive) value of a number by removing the negative sign if present.
ABS(number)
SELECT ABS(-123.45);
-- Output: 123.45
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)
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)
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
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)
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
Numeric Functions
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
Returns the absolute (positive) value of a number by removing the negative sign if present.
ABS(number)
SELECT ABS(-123.45);
-- Output: 123.45
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)
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)
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
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)
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
Numeric Functions
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
Returns the absolute (positive) value of a number by removing the negative sign if present.
ABS(number)
SELECT ABS(-123.45);
-- Output: 123.45
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)
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)
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
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)
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
Aggregate Functions
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)
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)
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"
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"
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)
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)
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)
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)
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
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)
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)
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"
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"
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)
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)
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)
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)
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
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)
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)
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"
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"
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)
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)
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)
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)
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
Creates a date value from specified year, month, and day integers.
DATEFROMPARTS(year, month, day)
SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'
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'
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)
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)
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)
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)
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)
Date Functions
Creates a date value from specified year, month, and day integers.
DATEFROMPARTS(year, month, day)
SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'
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'
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)
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)
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)
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)
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)
Date Functions
Creates a date value from specified year, month, and day integers.
DATEFROMPARTS(year, month, day)
SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'
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'
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)
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)
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)
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)
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)
Window Functions
Creates a date value from specified year, month, and day integers.
DATEFROMPARTS(year, month, day)
SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'
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'
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)
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)
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)
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)
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)
Window Functions
Creates a date value from specified year, month, and day integers.
DATEFROMPARTS(year, month, day)
SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'
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'
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)
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)
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)
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)
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)
Window Functions
Creates a date value from specified year, month, and day integers.
DATEFROMPARTS(year, month, day)
SELECT DATEFROMPARTS(2024, 12, 26);
-- Output: '2024-12-26'
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'
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)
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)
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)
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)
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)
One place for all your queries,
directly on your SQL editor
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