O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

images

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required