Link to home
Start Free TrialLog in
Avatar of MrDavidThorn
MrDavidThorn

asked on

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!
Avatar of Sean Stuber
Sean Stuber

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')
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I missed the format mask.  Good thing sdstuber posted before I did!
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.
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.
thanks!

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