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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top