SQL Functions
SQL offers multiple built-in functions that enable calculations on data.
SQL Aggregate Functions
SQL aggregate functions perform a calculation on a collection of values and return a single value.
The following table summarizes some useful aggregate functions:
| Data Type | Description |
|---|---|
| COUNT() | It returns the number of rows in a result set. |
| SUM() | It returns the sum of values. |
| AVG() | It returns the average of values. |
| MIN() | It returns the minimum value. |
| MAX() | It returns the maximum value. |
SQL String Functions
SQL string functions operate on a string input value and return a string or numeric value.
The following table summarizes some useful string functions:
| Function | Description |
|---|---|
| TRIM() | It removes leading and trailing spaces from a string. |
| CONCAT() | It returns a string by concatenating two or more string values. |
| CONCAT_WS() | It returns a string by concatenating two or more string values with a separator. |
| FORMAT() | It returns a value formatted with the specified format. |
| LOWER() | It converts a string to lowercase. |
| UPPER() | It converts a string to uppercase. |
| SUBSTRING() | It returns a substring from a string. |
| REVERSE() | It returns the reverse order of a string value. |
SQL Date Functions
SQL date functions are used to manipulate date values.
The date and time returned by the SQL date functions are the date and time of the server in which the database systems are running.
MySQL Date Functions
The following table summarizes some MySQL's built-in date functions:
| Function | Description |
|---|---|
| NOW() | It returns the current date and time. |
| CURTIME() | It returns the current time. |
| CURDATE() | It returns the current date. |
| DATE() | It extracts the date part of a date or datetime expression. |
| DAY() | It returns the day of the month (0-31). |
| DAYNAME() | It returns the name of the weekday. |
| MONTH() | It returns the month from the date passed (1-12). |
| MONTHNAME() | It returns the name of the month. |
| YEAR() | It returns the year. |
| EXTRACT() | It extracts part of a date. |
| DATE_FORMAT() | It displays date and time value in other formats. |
| DATE_ADD() | It adds a specified time value (or interval) to a date value. |
| DATE_SUB() | It subtracts a specified time value (or interval) from a date value. |
| DATEFIFF() | It returns the number of days between two dates. |
SQL Server Date Functions
The following table summarizes some SQL Server's built-in date functions:
| Function | Description |
|---|---|
| GETDATE() | It returns the current date and time. |
| DATEPART() | It returns the specified datepart of the specified date. Example DATEPART(year, '2018-03-26') return 2018. |
| DAY() | It returns the day of the month (0-31). |
| MONTH() | It returns the month from the specified date (0-12). |
| YEAR() | It returns the year from the specified date. |
| DATEADD() | It adds or subtracts a specified time interval from a date. |
| DATEDIFF() | It returns the date or time between two specified dates. |
| CONVERT() | It displays date and time value in other formats. |