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 and ( Month > 2 or ( Month = 2 and Day >= 15 )))) and ( Year < 2001 or ( Year = 2001 and ( 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 2Here 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.