Problem solve Get help with specific problems with your technologies, process and projects.

Working with dates where year, month and day are separate columns

I have a table of sales records that has year, month and day columns, which represent the date of sales. What is a SQL statement that will return all the records from a user specified date?

SELECT * FROM records
WHERE records.year > 1999 AND 
      records.month > 2 AND 
      records.day > 15 AND 
      records.year < 2001 AND
      records.month < 10 AND
      records.day < 2;
That statement won't work because it would eliminate dates like 2000/1/15 and 2000/12/3.

I will start by saying that most databases have fairly decent support for temporal data types. When possible, I use them in order to avoid reinventing the proverbial wheel. That said, I have a couple of solutions for you.

Here is an example table populated with sample data:

create table MyTable
( Year int,
  Month int,
  Day int

insert into MyTable values (1999,2,14);
insert into MyTable values (1999,2,15);
insert into MyTable values (2000,1,15);
insert into MyTable values (2000,12,3);
insert into MyTable values (2000,6,25);
insert into MyTable values (2001,5,19);
insert into MyTable values (2001,10,2);
insert into MyTable values (2001,10,3);
The first solution uses a relatively complex WHERE clause with ORs, ANDs and parentheses to filter the rows. If the goal is to find the rows with dates inclusively between 15-FEB-1999 and 02-OCT-2001, this would be the SQL statement:
select * from MyTable
  where ( Year > 1999 or ( Year = 1999 
                           ( Month > 2 or ( Month = 2 and 
                                            Day >= 15 ))))  
        ( Year < 2001 or ( Year = 2001
                           ( Month < 10 or ( Month = 10 and 
                                             Day <= 2 ))));
In English, this WHERE clause says: give me the rows where the year is greater than 1999; or if the year is 1999, give me the rows where the month is greater than 2; or if the month is equal to 2, then give me the rows where the day is greater than or equal to the 15th. In addition, the WHERE clause provides the upper bound of the range in a similar fashion.

Here are the resulting rows:

Year Month Day
---- ----- ---
1999     2  15
2000     1  15
2000    12   3
2000     6  25
2001     5  19
2001    10   2
Here is another much simpler solution which produces the same result. The idea behind this SQL is using an encoding scheme which produces, from a date, a number with a value which is significant when compared against other dates encoded the same way. This encoding scheme would have an affect similar to a Julianized date. Quite simply, the format of the numerically encoded date would be YYYYMMDD. Here's the SQL:
select * from MyTable
  where (Year*10000) + (Month*100) + Day between 19990215 
                                             and 20011002;

For More Information

  • What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL