ORACLE syntax for querying dates

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

SELECT  * FROM ORACLE.DB WHERE TO_DATE(ENTRY_DATE,'YYYY-MM-DD HH24:MI:SS) >=
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!
MrDavidThornAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
If ENTRY_DATE is a date in Oracle then do not use TO_DATE on it.


SELECT  * FROM ORACLE.DB WHERE ENTRY_DATE >=
TO_DATE('2011-10-10 00:00:01') AND ENTRY_DATE <= TO_DATE('2011-10-11 15:00:00')

0
 
sdstuberCommented:
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')
0
 
slightwv (䄆 Netminder) Commented:
I missed the format mask.  Good thing sdstuber posted before I did!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
sdstuberCommented:
thanks!

a split is fine, we posted at approx the same time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.