SQL Functions
SQL has many built-in functions that enable you to perform calculations on data.
SQL Aggregate Functions
SQL aggregate functions perform calculation on a set of values and return a single value. The following table summarizes some useful aggregate functions:
| Function | Description |
|---|---|
AVG() |
Returns the average of values |
SUM() |
Returns the sum of values |
COUNT() |
Returns the number of rows in a result set |
MAX() |
Returns the maximum value |
MIN() |
Returns the minimum value |
SQL String Functions
SQL string functions perform operation on a string input value and return a string or numeric value. The following table summarizes some useful string functions:
| Function | Description |
|---|---|
CONCAT() |
Returns a string by concatenating two or more string values. |
CONCAT_WS() |
Returns a string by concatenating two or more string values with a separator. |
FORMAT() |
Returns a value formatted with the specified format. |
LOWER() |
Converts a string to lowercase. |
UPPER() |
Converts a string to uppercase. |
TRIM() |
Remove leading and trailing spaces from a string. |
REVERSE() |
Returns the reverse order of a string value. |
SUBSTRING() |
Returns a substring from string. |
SQL Date Functions
Date functions are used to manipulate or perform operation on date values.
MySQL Date Functions
The following table summarizes some most important MySQL's built-in date functions:
| Function | Description |
|---|---|
NOW() |
Returns the current date and time. |
CURDATE() |
Returns the current date. |
CURTIME() |
Returns the current time |
DATE() |
Extract the date part of a date or datetime expression. |
DAY() |
Returns the day of the month (0-31). |
DAYNAME() |
Returns the name of the weekday. |
MONTH() |
Returns the month from the date passed (1-12). |
MONTHNAME() |
Returns the name of the month. |
YEAR() |
Returns the year. |
DATE_FORMAT() |
Displays date and time value in other formats. |
EXTRACT() |
Extract part of a date. |
DATE_ADD() |
Adds a specified time value (or interval) to a date value. |
DATE_SUB() |
Subtracts a specified time value (or interval) from a date value. |
DATEDIFF() |
Returns the number of days between two dates |
Note: The date and time returned by these functions is the date and time of the computer on which the instance of database server is running.
SQL Server Date Functions
The following table summarizes some most important SQL Server's built-in date functions:
| Function | Description |
|---|---|
GETDATE() |
Returns the current date and time. |
DATEPART() |
Returns the specified datepart of the specified date e.g. DATEPART(year,'2016-10-25') return 2016. |
DAY() |
Returns the day of the month (0-31). |
MONTH() |
Returns the month from the specified date (0-12). |
YEAR() |
Returns the year from the specified date. |
DATEADD() |
Adds or subtracts a specified time interval from a date. |
DATEDIFF() |
Returns the date or time between two specified dates. |
CONVERT() |
Displays date and time value in other formats. |
