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
http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html

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
http://dev.mysql.com/doc/refman/5.5/en/string-functions.html

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()	Returns 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(unix_timestamp) FROM_UNIXTIME(unix_timestamp, format)	Returns a representation of the UNIX_TIMESTAMP as a string in the form 'YYYY-MM-DD HH:MM:SS' or as number in the form YYYYMMDDHHMMSS.uuuuuu, depending on the context. If format is specified, the result put into that form, using the same specifiers as the DATE_FORMAT function.
HOUR(time)	Returns the hour value.
LAST_DAY(date)	Returns the last day of the month for the specified date.
MINUTE(time)	Returns the minute value.
MONTH(date)	Returns the month (1=January, 12=December).
MONTHNAME(date)	Returns the full name of the month.
NOW()	Returns the current date and time as a string in the form 'YYYY-MM-DD HH:MM:SS' or a number in the form YYYYMMDDHHMMSS.uuuuuu, depending on context.
SEC_TO_TIME(seconds)	Converts the specified number of seconds to a time value.
SECOND(time)	Returns the seconds from the specified time.
STR_TO_DATE(str, format)	Returns a date for a given string and the format to interpret it with, where format uses the same specifiers as the DATE_FORMAT function.
SUBTIME(expr, time)	Returns expr1 (a time or date) minus expr2 (a time), in the same format as expr1. Note: In the platform, a time and a date/time are stored the same way, as a Date/Time. To send a time value to this function, use the SQL TIME() function to extract the value from such fields. For example: SELECT TIMEDIFF(TIME(date_time_fld), TIME(time_fld)) AS elapsed_time FROM Customer
TIME(expr)	Returns the time portion of the date/time expression.
TIME_TO_SEC(time)	Converts the specified time to seconds.
TIMEDIFF(expr1, expr2)	Returns expr1 minus expr2 expressed as a time value. (Both expr1 and expr2 must be of the same type: time, or date-and-time.) Note: In the platform, a time and a date/time are stored the same way, as a Date/Time. To send a time value to this function, use the SQL TIME() function to extract the value from such fields. For example: SELECT TIMEDIFF(TIME(date_time_fld), TIME(time_fld)) AS new_date FROM Customer
TO_DAYS(date)	Converts the date to the number of days since year 0.
UNIX_TIMESTAMP() UNIX_TIMESTAMP(expr)	With no argument, returns a Unix timestamp--an unsigned integer containing the number of seconds since the "zero-date" ('1970-01-01 00:00:00' UTC). If expr is specified, it is converted to the number of seconds since the zero-date. Expr can be a string containing a date, date-time, or timestamp; Or it can be a number in the form YYMMDD or YYYYMMDD.
YEAR(date)	Returns the year for the specified date, in the range 1000 to 9999, Returns 0 for the “zero date" ('1970-01-01 00:00:00' UTC).

Learn more:

MySQL Date and Time Functions
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

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
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html