Help required in pulling records for a particular date and time?

Posted on 2011-05-04
Last Modified: 2012-05-11
Hi Experts,

Could you please help me in executing the following query:

Select * from RELAY_POLLER_LOG where lot_type_fk=1 and actual_date between '03-May-2011' and '04-May-2011'

I am using Oracle 10g as the database.

Question by:dileepav
    LVL 73

    Expert Comment

    first, you are using implicit conversion to treat your strings as dates.  don't do that,  always (really, ALWAYS) use explicit conversions

    try this...

    Select * from RELAY_POLLER_LOG
    where lot_type_fk=1
    and actual_date  between to_date('03-May-2011','dd-Mon-yyyy') and to_date('04-May-2011','dd-Mon-yyyy')

    if this doesn't work,  please post the errors or other results
    LVL 76

    Accepted Solution

    Assuming actual_date is an Oracle date, do explicit date conversions:

    between to_date('03-May-2011','DD-Mon-YYYY') and to_date('04-May-2011','DD-Mon-YYYY')

    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    I can only confirm the date format stuff:

    Author Closing Comment

    Thx Experts
    LVL 73

    Expert Comment

    if there are equivalent answers posted at the same time you should split among all of them

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    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

    14 Experts available now in Live!

    Get 1:1 Help Now