View source for SQL Functions

These functions can be used in your [[SQL Syntax|SQL]] statements.
{{Tip|When returning a function value in a SQL [[SQL Syntax#SELECT Statement|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
:: <tt>SELECT CURDATE() <b>AS Date</b> FROM Customer</tt> 
::: - returns the current date, under the heading "Date", once for each record in the Customer object.
:: <tt>SELECT CURDATE() <b>AS Date</b> FROM Customer LIMIT 1</tt> 
::: - returns the current date, under the heading "Date", one time.}}
__TOC__
{{:Common:SQL Legend}}
===Logical Functions===
:{| border="1" cellpadding="5" cellspacing="1" 
| '''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<br>http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/control-flow-functions.html

===String Functions===
:{| border="1" cellpadding="5" cellspacing="1" 
| '''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<br>http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/string-functions.html

===Mathematical Functions===
:{| border="1" cellpadding="5" cellspacing="1" 
| '''ROUND(''x'')''' || Round ''x'' up or down to the nearest integer value.
|-
| '''ROUND(''x'', ''d'')''' ||  Round ''x''to ''d'' decimal places.
|}

:''Learn more:'' [http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/mathematical-functions.html MySQL Mathematical Functions]

===Date and Time Functions===
:{| border="1" cellpadding="5" cellspacing="1" 
|-
| valign="top"| '''CURDATE()''' || Returns current date as a string or number, depending on current context. String format: <tt>'YYYY-MM-DD'</tt>. Numeric format: <tt>YYYYMMDD</tt>.
|-
| valign="top"|  '''CURTIME()''' || Returns current time as a string or number, depending on current context. String format: <tt>'HH:MM:SS'</tt>. Numeric format: <tt>HHMMSS.uuuuuu</tt>, where <tt>uuuuuu</tt> is the number of microseconds.
|-
| valign="top"| '''<span id="DATE_ADD">DATE_ADD</span>(''date'',<br> &nbsp;&nbsp;INTERVAL&nbsp;''expr''&nbsp;''unit'')'''<br>
<br>
''' DATE_SUB(''date'',<br> &nbsp;&nbsp;INTERVAL ''expr'' ''unit'')'''
| Add ''expr'' units to a time value (or subtract from it).<br>
Where: ''expr'' can be a negative number, and where:
:{| border="1" cellpadding="5" cellspacing="1" 
! ''unit'' !! Expression Type !! Expression Format
|-
|MICROSECOND<br>
SECOND<br>
MINUTE<br>
HOUR<br>
DAY<br>
WEEK<br>
MONTH<br>
QUARTER<br>
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'
|}
|-
| valign="top"| '''DATE(''expr'')''' || Returns the date from a date/time expression.
|-
| valign="top"| '''<span id="DATE_FORMAT">DATE_FORMAT</span>(''date'', ''format'')''' || Puts the date into the specified format. 
{| border="1" cellpadding="5" cellspacing="1" 
! 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
|}
|-
| valign="top"| '''DATEDIFF(''expr1, expr2'')''' || Returns the number of days between the two date or date/time expressions. (Only the date portions are used.)
|-
| valign="top"| '''DAYNAME(''date'')''' || Returns the name of the weekday for the specified date.
|-
| valign="top"| '''DAYOFMONTH(''date'')''' || Returns the day of the month for the specified date.
|-
| valign="top"| '''DAYOFWEEK(''date'')''' || Returns the weekday index for the specified date (1=Sunday, 7=Saturday).
|-
| valign="top"| '''DAYOFYEAR(''date'')''' || Returns the day of the year for the specified date (1 to 366).
|-
| valign="top"| '''EXTRACT(''unit'' FROM ''date'')''' || Extracts the specified part of the date, using the same unit-specifiers as [[#DATE_ADD|DATE_ADD]].
|-
| valign="top"| '''FROM_UNIXTIME(''unix_timestamp'')'''<br>
'''FROM_UNIXTIME(''unix_timestamp'', ''format'')''' 
| Returns a representation of the [[#UNIX_TIMESTAMP|UNIX_TIMESTAMP]] as a string in the form <tt>'YYYY-MM-DD HH:MM:SS'</tt> or as number in the form <tt>YYYYMMDDHHMMSS.uuuuuu</tt>, depending on the context. If ''format'' is specified, the result put into that form, using the same specifiers as the [[#DATE_FORMAT|DATE_FORMAT]] function.
|-
| valign="top"| '''HOUR(''time'')''' || Returns the hour value.
|-
| valign="top"| '''LAST_DAY(''date'')''' || Returns the last day of the month for the specified date.
|-
| valign="top"| '''MINUTE(''time'')''' || Returns the minute value.
|-
| valign="top"| '''MONTH(''date'')''' || Returns the month (1=January, 12=December).
|-
| valign="top"| '''MONTHNAME(''date'')''' || Returns the full name of the month.
|-
| valign="top"| '''NOW()''' || Returns the current date and time as a string in the form <tt>'YYYY-MM-DD HH:MM:SS'</tt> or a number in the form <tt>YYYYMMDDHHMMSS.uuuuuu</tt>, depending on context.
|-
| valign="top"| '''SEC_TO_TIME(''seconds'')''' || Converts the specified number of seconds to a time value.
|-
| valign="top"| '''SECOND(''time'')''' || Returns the seconds from the specified time.
|-
| valign="top"| '''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|DATE_FORMAT]] function.
|-
| valign="top"| '''SUBTIME(''expr, time'')''' || Returns ''expr1'' (a time or date) minus ''expr2'' (a time), in the same format as ''expr1''.

:'''Note:'''<br>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 <tt>TIME()</tt> function to extract the value from such fields.
:For example:
::<tt>SELECT TIMEDIFF(TIME(date_time_fld), TIME(time_fld)) AS elapsed_time</tt>
::: <tt>FROM Customer</tt>

|-
| valign="top"| '''TIME(''expr'')''' || Returns the time portion of the date/time expression. 
|-
| valign="top"| '''TIME_TO_SEC(''time'')''' || Converts the specified time to seconds.
|-
| valign="top"| '''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:'''<br>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 <tt>TIME()</tt> function to extract the value from such fields.
:For example:
::<tt>SELECT TIMEDIFF(TIME(date_time_fld), TIME(time_fld)) AS new_date</tt>
::: <tt>FROM Customer</tt>

|-
| valign="top"| '''TO_DAYS(''date'')''' || Converts the date to the number of days since year 0.
|-
| valign="top"| '''UNIX_TIMESTAMP()'''<br>
'''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 <tt>YYMMDD</tt> or <tt>YYYYMMDD</tt>.
|-
| valign="top"| '''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<br>http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/date-and-time-functions.html 

===Aggregate Functions===
:{| border="1" cellpadding="5" cellspacing="1" 
| '''SUM(''expr'')'''<br>'''SUM(DISTINCT&nbsp;''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'')'''<br>'''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 [[SQL Syntax#SELECT Statement|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<br> &nbsp; ''expr'' [, ''expr'' ...])''' || Returns a count of rows with different non-NULL ''expr'' values.
|-
| '''MAX(''expr'')'''<br>'''MAX(DISTINCT&nbsp;''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'')'''<br>'''MIN(DISTINCT&nbsp;''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<br>http://dev.mysql.com/doc/refman/{{MySqlVersion}}/en/group-by-functions.html