Discovering SQL: A Hands-On Guide for Beginners

DATE AND TIME FUNCTIONS

Date and time functions are some of the most useful yet confusing functions ever provided by the RDBMS. The SQL Standard does not mandate which have to be implemented, so the vendors and organizations building RDBMSs, left to their own devices, implemented a huge variety of DATE and TIME functions.

What Time Is It?

Keeping a time track of the changes in the database requires access to the system's date and time settings. Oracle implemented the SYSDATE pseudo-column (which can be considered a function for our purposes), which returns the system's current date and time. Microsoft SQL Server has the GETDATE() function, and IBM DB2 9.7 consistently uses a CURRENTDATE special register in the SELECT part of the query. These functions (with a sample of their respective outputs) are listed in Table 4-3.

TABLE 4-3: Getting the Current Date from RDBMSs

The date output can be formatted using various vendor-specific masks, arguments, or conversion functions; please refer to the RDBMS manual for more information on formatting. Knowing the date and time is half the battle; you need the capability to extract date parts, day, year, and months, to be able to manipulate them in your queries. One way to do it is to convert the date into a string and parse it using string functions, or you can use a shortcut and turn to the functions already implemented by the RDBMS.

For instance, ...