Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Locating Date Data

Date data can be hard to retrieve because of the variety of possible ways it can be stored. In many systems, date columns include the month, day, year, and time of day (the first three fields can be displayed in a number of ways).

This presents some problems in matching the time part of the column. One example is a query like this:

SQL

select price, pubdate
from titles
where pubdate = 'Oct 21 1998'
     price pubdate
========== ====================
     40.95 Oct 21 1998 12:00 am
     41.59 Oct 21 1998 12:00 am
[2 rows]

This query finds records with a date of October 21, 1998, and a time of 12:00 A.M. To get all records from any time on that date, you must add some more code. To test the solutions, first change one of the dates:

SQL

 update ...