Arithmetic Operations Supported
Each database system has a variety of functions that you can use to calculate or manipulate date and time values. Each database system also has its own rules regarding data types and date and time arithmetic. The SQL Standard specifically defines three functions, CURRENT_DATE(), CURRENT_TIME(), and CURRENT_TIMESTAMP(), but many commercial database systems do not support all three function calls. To help you work with date and time values in your database system, we provide a brief summary of the data types and arithmetic operations supported. Following that, we have compiled a list of functions for several of the major database systems that you can use to work with date and time values. The lists in this appendix include the function name and a brief description of its use.1 Consult your database documentation for the specific syntax to use with each function.
1. Most of this material previously appeared in SQL Queries for Mere Mortals, Third Edition by John L. Viescas and Michael J. Hernandez (Addison-Wesley, 2014).
DATE
TIME
TIMESTAMP
Note
Although the Access user interface displays the data type name as Date/Time, the correct name for CREATE TABLE statements is DATETIME.
time
smalldatetime
datetime
datetime2
datetimeoffset
DATETIME
TIMESTAMP
TIME
YEAR
The syntax for intervals is INTERVAL <expr> <unit>, where <unit> is one of the keywords listed above, as in INTERVAL 31 day or INTERVAL 15 minute.
It is also legal to add an integer of decimal value to or subtract it from any of the date and time data types, but MySQL first converts the date or time value to a number and then performs the operation. For example, adding 30 to the date value 2012-11-15 yields the number 20121145. Adding 100 to the time value 12:20:00 yields 122100. Be sure to use the INTERVAL keyword when performing date and time arithmetic.
TIMESTAMP
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
TIME (with or without time zone)
TIMESTAMP (with or without time zone)
INTERVAL
