ORACLE syntax for querying dates

Posted on 2011-10-11
Medium Priority
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
  • 3
  • 2
LVL 74

Expert Comment

ID: 36950382
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 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 36950385
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36950393
I missed the format mask.  Good thing sdstuber posted before I did!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 35

Expert Comment

by:Mark Geerlings
ID: 36950448
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36950522
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 74

Expert Comment

ID: 36950526

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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

571 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