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-M M-DD HH24:MI:SS) >=
TO_DATE('2011-10-10 00:00:01') AND TO_DATE(ENTRY_DATE,'YYYY-M M-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!
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-M
TO_DATE('2011-10-10 00:00:01') AND TO_DATE(ENTRY_DATE,'YYYY-M
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
a split is fine, we posted at approx the same time
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')