ORACLE syntax for querying dates

Posted on 2011-10-11
Last Modified: 2012-05-12
Hi Experts

I am using a linked server on SQL 2005 to Oracle, Im using the openquery command to return a select query

I have a time date stamp field and want to return records with in a date and time range
so far I have

TO_DATE('2011-10-10 00:00:01') AND TO_DATE(ENTRY_DATE,'YYYY-MM-DD  HH24:MI:SS) <= TO_DATE('2011-10-11 15:00:00')

Although there are records in the table none are being returned, does anyone know why this could be, any help would be great beacuse Im not upto speed with my Oracle syntax knowledge!
Question by:MrDavidThorn
    LVL 73

    Expert Comment

    is entry_date a DATE or a string (CHAR/VARCHAR2)  type?

    if it's a DATE,  then don't use TO_DATE on it.

    also, you should supply a format for your TO_DATE's

    TO_DATE('2011-10-10 00:00:01','yyyy-mm-dd hh24:mi:ss')
    LVL 76

    Accepted Solution

    If ENTRY_DATE is a date in Oracle then do not use TO_DATE on it.

    TO_DATE('2011-10-10 00:00:01') AND ENTRY_DATE <= TO_DATE('2011-10-11 15:00:00')

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    I missed the format mask.  Good thing sdstuber posted before I did!
    LVL 34

    Expert Comment

    by:Mark Geerlings
    Don't use "to_date" on the actual database column in your query if that column really is a "date" datatype in the Oracle database table because using any operator (like: to_char, to_date, upper, lower, trunc,etc.) will prevent Oracle from using an index on that column.  If the table is large and if this column is indexed, not using the index can make your query take 100s or thousands of more time than if Oracle can use the index.  And yes, you should always provide a format mask when you use "to_date" with literal values, to make sure that Oracle converts your character string to a date correctly.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Although I appreciate the points, sdstuber posted first and actually caught the format mask.

    If you wish to give me some of the points, he should at least get the split.

    Just let us know and we can reopen the question for you.
    LVL 73

    Expert Comment


    a split is fine, we posted at approx the same time

    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

    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    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

    16 Experts available now in Live!

    Get 1:1 Help Now