Difference between revisions of "SQL Functions"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 85: Line 85:
|}
|}
|-
|-
| valign="top"| '''DATE''' ||  
| valign="top"| '''DATE(''expr'')''' || Returns the date from a date/time expression.
|-
|-
| valign="top"| '''DATE_FORMAT''' ||  
| valign="top"| '''DATE_FORMAT(''date'',''format'')''' || Puts the date into the specified format.
{| border="1" cellpadding="5" cellspacing="1"
| Format Specifier | Description
|-
|-
| valign="top"| '''DATEDIFF''' ||  
| %a || Abbreviated weekday name (Sun..Sat)
|-
|-
| valign="top"| '''DAYNAME''' ||  
| %b || Abbreviated month name (Jan..Dec)
|-
|-
| valign="top"| '''DAYOFMONTH''' ||  
| %c || Month, numeric (0..12)
|-
|-
| valign="top"| '''DAYOFWEEK''' ||  
| %D || Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
|-
|-
| valign="top"| '''DAYOFYEAR''' ||  
| %d || Day of the month, numeric (00..31)
|-
|-
| valign="top"| '''EXTRACT''' ||  
| %e || Day of the month, numeric (0..31)
|-
|-
| valign="top"| '''FROM_UNIXTIME''' ||  
| %f || Microseconds (000000..999999)
|-
|-
| valign="top"| '''HOUR''' ||  
| %H || Hour (00..23)
|-
|-
| valign="top"| '''LAST_DAY''' ||  
| %h || Hour (01..12)
|-
|-
| valign="top"| '''MINUTE''' ||  
| %I || Hour (01..12)
|-
|-
| valign="top"| '''MONTH''' ||  
| %i || Minutes, numeric (00..59)
|-
|-
| valign="top"| '''MONTHNAME''' ||  
| %j || Day of year (001..366)
|-
|-
| valign="top"| '''NOW''' ||  
| %k || Hour (0..23)
|-
|-
| valign="top"| '''SEC_TO_TIME''' ||  
| %l || Hour (1..12)
|-
|-
| valign="top"| '''SECOND''' ||  
| %M || Month name (January..December)
|-
|-
| valign="top"| '''STR_TO_DATE''' ||  
| %m || Month, numeric (00..12)
|-
|-
| valign="top"| '''SUBTIME''' ||  
| %p || AM or PM
|-
|-
| valign="top"| '''TIME''' ||  
| %r || Time, 12-hour (hh:mm:ss followed by AM or PM)
|-
|-
| valign="top"| '''TIME_TO_SEC''' ||  
| %S || Seconds (00..59)
|-
|-
| valign="top"| '''TIMEDIFF''' ||  
| %s || Seconds (00..59)
|-
|-
| valign="top"| '''TO_DAYS''' ||  
| %T || Time, 24-hour (hh:mm:ss)
|-
|-
| valign="top"| '''UNIX_TIMESTAMP''' ||  
| %U || Week (00..53), where Sunday is the first day of the week
|-
|-
| valign="top"| '''YEAR''' ||  
| %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(''expr'')''' ||
|-
| valign="top"| '''DAYOFMONTH(''expr'')''' ||
|-
| valign="top"| '''DAYOFWEEK(''expr'')''' ||
|-
| valign="top"| '''DAYOFYEAR(''expr'')''' ||
|-
| valign="top"| '''EXTRACT(''expr'')''' ||
|-
| valign="top"| '''FROM_UNIXTIME(''expr'')''' ||
|-
| valign="top"| '''HOUR(''expr'')''' ||
|-
| valign="top"| '''LAST_DAY(''expr'')''' ||
|-
| valign="top"| '''MINUTE(''expr'')''' ||
|-
| valign="top"| '''MONTH(''expr'')''' ||
|-
| valign="top"| '''MONTHNAME(''expr'')''' ||
|-
| valign="top"| '''NOW(''expr'')''' ||
|-
| valign="top"| '''SEC_TO_TIME(''expr'')''' ||
|-
| valign="top"| '''SECOND(''expr'')''' ||
|-
| valign="top"| '''STR_TO_DATE(''expr'')''' ||
|-
| valign="top"| '''SUBTIME(''expr'')''' ||
|-
| valign="top"| '''TIME(''expr'')''' ||
|-
| valign="top"| '''TIME_TO_SEC(''expr'')''' ||
|-
| valign="top"| '''TIMEDIFF(''expr'')''' ||
|-
| valign="top"| '''TO_DAYS(''expr'')''' ||
|-
| valign="top"| '''UNIX_TIMESTAMP(''expr'')''' ||
|-
| valign="top"| '''YEAR(''expr'')''' ||  
|}         
|}         
:''Learn more:'' [http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html MySQL Date and Time Functions]
:''Learn more:'' [http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html MySQL Date and Time Functions]

Revision as of 00:08, 12 November 2011

These functions can be used in your SQL statements.

Thumbsup.gif

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.
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(expr)
DAYOFMONTH(expr)
DAYOFWEEK(expr)
DAYOFYEAR(expr)
EXTRACT(expr)
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