SQL Cookbook, 2nd Edition

Chapter 8. Date Arithmetic

This chapter introduces techniques for performing simple date arithmetic. Recipes cover common tasks such as adding days to dates, finding the number of business days between dates, and finding the difference between dates in days.

Being able to successfully manipulate dates with your RDBMS’s built-in functions can greatly improve your productivity. For all the recipes in this chapter, we try to take advantage of each RDBMS’s built-in functions. In addition, we have chosen to use one date format for all the recipes, DD-MON-YYYY. Of course, there are a number of other commonly used formats, such as DD-MM-YYYY, the ISO standard format.

We chose to standardize on DD-MON-YYYY to benefit those of you who work with one RDBMS and want to learn others. Seeing one standard format will help you focus on the different techniques and functions provided by each RDBMS without having to worry about default date formats.

Tip

This chapter focuses on basic date arithmetic. You’ll find more advanced date recipes in the following chapter. The recipes presented in this chapter use simple date data types. If you are using more complex date data types, you will need to adjust the solutions accordingly.

8.1 Adding and Subtracting Days, Months, and Years

Problem

You need to add or subtract some number of days, months, or years from a date. For example, using the HIREDATE for employee CLARK, you want to return six different dates: five days before and after CLARK was hired, ...