Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: