| Lesson 11 | Date functions |
| Objective | Understand why date functions are used. |
SQL Date Functions
As soon as you start building report queries, you will find that dates will be an integral part of them.
Dates will help you summarize information, segmenting the results into readable segments that can be analyzed.
The clauses are pretty straightforward, typically requiring only a few different parameters to be passed to the function call.
After you have used them once or twice, you will quickly find out how powerful dates are, and how they can really begin to impact the ease with which you summarize and present the information in your reports.
The next lesson covers several important points to keep in mind while working with date functions.
MySQL Date Functions
The following table lists the most important built-in date functions in MySQL:
| Function |
Description |
| NOW() |
Returns the current date and time |
| CURDATE() |
Returns the current date |
| CURTIME() |
Returns the current time |
| DATE() |
Extracts the date part of a date or date/time expression |
| EXTRACT() |
Returns a single part of a date/time |
| DATE_ADD() |
Adds a specified time interval to a date |
| DATE_SUB() |
Subtracts a specified time interval from a date |
| DATEDIFF() |
Returns the number of days between two dates |
| DATE_FORMAT() |
Displays date/time data in different formats |
- DATE Stores a date like June 30, 2018
- TIME Stores a time of day like 12:30:00 P.M.
- TIMESTAMP A specific instant in history, with a precision down to the nanosecond
Specific dates and times can be specified as string constants, and date arithmetic is supported. Here is an example of a valid query using DB2 dates,
assuming that the HIRE_DATE column contains DATE data:
SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE HIRE_DATE >= '05/30/2017' + 15 DAYS;
SQL Server was introduced with a single date/time data type, called DATETIME, which closely resembles the DB2 TIMESTAMP data type.
If HIRE_DATE contained DATETIME data, SQL Server could accept this version of the query (without the date arithmetic):
SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE HIRE_DATE >= '06/14/2017';
Since no specific time on June 14, 2017, is specified in the query, SQL Server defaults to midnight on that date. The SQL Server query thus really means
SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE HIRE_DATE >= '06/14/2017 12:00AM';
SQL Server also supports date arithmetic through a set of built-in functions. Thus, the DB2-style query can also be specified in this way:
SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE HIRE_DATE >= DATEADD(DAY, 15, '05/30/2017')
which is considerably different from the DB2 syntax. Oracle has long supported date/time data with a single data type called DATE. (Note, however, that Oracle added support for the SQL Standard DATETIME and TIMESTAMP
data types starting with Oracle 9i.) Like SQL Server's DATETIME type, an Oracle DATE is, in fact, a timestamp. Also as with SQL Server, the time part of an Oracle DATE value defaults to midnight if no time is explicitly specified.
The default Oracle date format is different from the DB2 and SQL Server formats, so the Oracle version of the query becomes
SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE HIRE_DATE >= '14-JUN-17';
Oracle also supports limited date arithmetic, so the DB2-style query can also be specified, but without the DAYS keyword:
SELECT NAME, HIRE_DATE
FROM SALESREPS
WHERE HIRE_DATE >= '30-MAY-17' + 15;
Note, however, that this statement requires the DBMS to implicitly convert the string to an appropriate date data type before adding 15 to it, and that not all SQL implementationssupport such conversion. Oracle, for example, will report an error unless a function such as
TO_DATE or CAST converts the character string to an Oracle DATE or DATETIME type before attempting date arithmetic.