SQL Functions

These functions can be used in your SQL statements.

Tip: When returning a function value in a SQL SELECT Statement: It's necessary to specify a table to select from. It's generally necessary to include an AS clause, so the value has a handle. It's a good idea to include a LIMIT clause, as well SELECT CURDATE() AS Date FROM Customer - returns the current date, under the heading "Date", once for each record in the Customer object. SELECT CURDATE() AS Date FROM Customer LIMIT 1 - returns the current date, under the heading "Date", one time.

Legend

• [ x ] - Optional (one or none)
• [, x ... ] - Optional additional values, in a comma-separated list
• x | y - Choose one. Curly braces are added when needed: { x | y } ...
• CAPITALIZED - SQL Keyword (case insensitive)
• italicized - Value you supply. (Table names and column names are case-sensitive)

Logical Functions

IF(expr1,expr2,expr3)	If expr1 is not zero and not null, return expr2. Otherwise, return expr3.
IFNULL(expr1,expr2)	If expr1 is not NULL, return expr1; Otherwise, return expr2.

Learn more: MySQL Control Flow Functions

String Functions

CONCAT(str1,str2,...)	Concatenates the strings. Returns the result.
LENGTH(str)	Returns the length of the string, in bytes. (A mult-byte character counts as multiple bytes.)

Learn more: MySQL String Functions

Mathematical Functions

ROUND(x)	Round x up or down to the nearest integer value.
ROUND(x, d)	Round xto d decimal places.

Learn more: MySQL Mathematical Functions

Date and Time Functions

CURDATE()	eturns current date as a string or number, depending on current context. String format: 'YYYY-MM-DD'. Numeric format: YYYYMMDD.
CURTIME()	Returns current time as a string or number, depending on current context. String format: 'HH:MM:SS'. Numeric format: HHMMSS.uuuuuu, where uuuuuu is the number of microseconds.
DATE_ADD(date,   INTERVAL expr unit) DATE_SUB(date,   INTERVAL expr unit)	Add expr units to a time value (or subtract from it). Where: expr can be a negative number, and where: unit Expression Type Expression Format MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEARS Number An integer specifying the number of units. SECOND_MICROSECOND String 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND String 'MINUTES:SECONDS.MICROSECONDS' MINUTE_SECOND String 'MINUTES:SECONDS' HOUR_MICROSECOND String 'HOURS:MINUTES:SECONDS.MICROSECONDS' HOUR_SECOND String 'HOURS:MINUTES:SECONDS' HOUR_MINUTE String 'HOURS:MINUTES' DAY_MICROSECOND String 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' DAY_SECOND String 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE String 'DAYS HOURS:MINUTES' DAY_HOUR String 'DAYS HOURS' YEAR_MONTH String 'YEARS-MONTHS'
DATE(expr)	Returns the date from a date/time expression.
DATE_FORMAT(date,format)	Puts the date into the specified format. Format Specifier Description %a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week %u Week (00..53), where Monday is the first day of the week %V Week (01..53), where Sunday is the first day of the week; used with %X %v Week (01..53), where Monday is the first day of the week; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal “%” character %x x, for any “x” not listed above
DATEDIFF(expr1,expr2)	Returns the number of days between the two date or date/time expressions. (Only the date portions are used.)
DAYNAME(date)	Returns the name of the weekday for the specified date.
DAYOFMONTH(date)	Returns the day of the month for the specified date.
DAYOFWEEK(date)	Returns the weekday index for the specified date (1=Sunday, 7=Saturday).
DAYOFYEAR(date)	Returns the day of the year for the specified date (1 to 366).
EXTRACT(unit FROM date)	Extracts the specified part of the date, using the same unit-specifiers as DATE_ADD.
FROM_UNIXTIME(expr)
HOUR(expr)
LAST_DAY(expr)
MINUTE(expr)
MONTH(expr)
MONTHNAME(expr)
NOW(expr)
SEC_TO_TIME(expr)
SECOND(expr)
STR_TO_DATE(expr)
SUBTIME(expr)
TIME(expr)
TIME_TO_SEC(expr)
TIMEDIFF(expr)
TO_DAYS(expr)
UNIX_TIMESTAMP(expr)
YEAR(expr)

Learn more: MySQL Date and Time Functions

Aggregate Functions

SUM(expr) SUM(DISTINCT expr)	Returns the sum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
AVG(expr) AVG(DISTINCT expr)	Returns the average of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
COUNT(expr)	Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement Returns 0 if there were no matching rows.
COUNT(*)	Returns a count of rows, whether or not they contain NULL values.
COUNT(DISTINCT   expr [, expr ...])	Returns a count of rows with different non-NULL expr values.
MAX(expr) MAX(DISTINCT expr)	Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
MIN(expr) MIN(DISTINCT expr)	Returns the maximum of the values in the expression, or of the distinct values in the expression. Returns NULL if the expression has no values.
STD(expr)	Returns the standard deviation of expr.
VARIANCE(expr)	Returns the standard variance of expr.

Learn more: MySQL Aggregate Functions