Basic SQL Case Statement for Date/Time Ranges

Posted on 2007-07-23
Last Modified: 2008-01-09
I am writing an SQL with "Oracle SQL Developer" to search for data based on user selection.  The two choices are:  6AM-3PM (1st Production Shift) and 3PM-12AM (2nd Production Shift).  I am ok with writing two separate SQLs to complete this task, but I would like to have one SQL making the decision based on a prompt for user input (i.e. Please enter shift "1" or "2").  Can someone please help me with the details of setting up this CASE?
Question by:Derek_Indianapolis
    LVL 12

    Expert Comment

    You can use construction
    WHEN (myparam=1 AND condition for case 1>) OR (m\yparam=2 and <second case condition>)

    Author Comment

    That leaves quite a bit to the imagination, perhaps I should give more details.  Here is the sql, with attempting to use the above outline...

    SELECT *
    FROM inventory_transactions a
    WHERE when(myparam=1 and a.editdate = '&&date')
                    OR (myparam=2 and a.editdate ='&&date')

    1. I would like the "myparam" to prompt the user for input when the SQL is run.
    2. Typically, I use the '&' to force user input, and from my understanding the double '&' retains the initial input for future use throughout the SQL.  This means I need to user prompts one for "myparam" and one for 'Date/Time Range"

    I am using a "Date/TIme" field to find the period of time (shift 1 and shift 2) of records to select.  The sql above does not work, as I am sure that I'm not using appropriate syntax and should probably be putting the statement under a different clause like SELECT
    LVL 12

    Accepted Solution

    Sorry for my misstype WHEN should be WHERE so you dont need "when" word in the query.

    I have no knowledge of your database or tool used so could not help with interfacing with user.
    If the date is fixed string you should hardcode it into query and let choose use only 1 or 2 possibility. it is why I put myparam as selector into query.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now