SQL Cookbook, 2nd Edition

Chapter 9. Date Manipulation

This chapter introduces recipes for searching and modifying dates. Queries involving dates are very common. Thus, you need to know how to think when working with dates, and you need to have a good understanding of the functions that your RDBMS platform provides for manipulating them. The recipes in this chapter form an important foundation for future work as you move on to more complex queries involving not only dates, but times, too.

Before getting into the recipes, we want to reinforce the concept (mentioned in the preface) of using these solutions as guidelines to solving your specific problems. Try to think “big picture.” For example, if a recipe solves a problem for the current month, keep in mind that you may be able to use the recipe for any month (with minor modifications), not just the month used in the recipe. Again, these recipes are guidelines, the absolute final option. There’s no possible way a book can contain an answer for all your problems, but if you understand what is presented here, modifying these solutions to fit your needs is trivial. Also consider alternative versions of these solutions. For instance, if the solution uses one particular function provided by your RDBMS, it is worth the time and effort to find out if there is an alternative—maybe one that is more or less efficient than what is presented here. Knowing your options will make you a better SQL programmer.

Tip

The recipes presented in this chapter use simple date data ...