Q

Querying records between dates

How do I query for records between sysdate and sysdate minus seven days? I'm accessing Oracle through VB.net for

the purpose of scheduling Crystal Reports. You can query such a date range as follows:

SELECT * FROM my_table
WHERE date_column BETWEEN SYSDATE and SYSDATE-7;

The problem typically comes in when using something like VB.NET and it's ODBC drivers. Oracle's ODBC drivers should have no problems with the above query. You can install Oracle client software and use Oracle's ODBC drivers. Or, you can create a view in the database as follows:

CREATE VIEW my_table_last_week
AS SELECT * FROM my_table
WHERE date_column BETWEEN SYSDATE and SYSDATE-7;

Now have your VB.NET application just query the view as follows:

SELECT * FROM my_table_last_week;

All ODBC drivers should be able to handle the above query.

This was first published in June 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close