This content has been marked as final. Show 8 replies
Welcome to the forums!
In cases like this it is helpful if you can provide the following information:
1. Oracle version (SELECT * FROM V$VERSION)
2. Sample data in the form of CREATE / INSERT statements.
3. Expected output
4. Explanation of expected output (A.K.A. "business logic")
5. Use \
tags for #2 and #3. See FAQ (Link on top right side) for details. I'll try and take a stab at your request based on the data given. What your query says is that it will return all rows that have a date greater then 2/1/2010 (MM/DD/YYYY). If your query is returning all rows then maybe the possibility exists that all the dates in the table are greater then 2/1/2010. Have you checked all dates to see if this is the case? Also, one note about your TO_DATE() function.
The date format does not match the string you are using with respect to month. Your string has 'FEB' but the format is 'MM' which is the numeric representation of the month. Although Oracle was able to convert it to the proper date on my system you should try and maintain consistency between the string and the date format used.
Welcome to the forum!
As Centinul said, don't confuse the format elements
'MM' (where '02' means February), and
'MON' (where "FEB" means February, depending on NLS_DATE_LANGUAGE).
Queries are more efficient if, in the WHERE clause, you use indexed columns all by themselves on one side of an operator, such as >
So, in the query you posted TRUNC is making the query inefficient. Since you're comparing the DATE from the table to an expresssion that includes hours, minutes and seconds , you probably don;'t want to use TRUNC anyway.
You probably want something like this:
SELECT * FROM table_x -- TABLE is not a good table name WHERE user_date >= TO_DATE ( 'FEB-01-2010 00:00:00 AM', , 'MON-DD-YYYY HH:MI:SS AM' ) ORDER BY user_date ASC ;
Thanks for your response. Sorry that I didn't follow the proper format.
1. I've been told its 10g, sorry I have limited information. If it is really critical I will ask them again. I don't use Toad or any other software, I basically created a page with VBSCRIPT that returns the fields with a random entry so that I can navigate all the tables and their rows.
2. For this project I only need SELECT privileges and that's the only thing I have. Sorry :(
4. I am trying to pull info from DB and populate them in a csv file and will use that file to feed data into another system. There are a lot of tables and I've been using nested queries. For example:
Everything is fine and dandy until i put date in where clause. When i compare number to filter my query it works fine.
SELECT id, name, zip, (SELECT date_start, date_end from table3 where date_id = table1.id) from table1
What I want to do is be able to pull data (return all rows) between certain date or all the data after the supplied date.
SELECT * from table1 where category = 1
Sorry for the wrong example i provided. I tried changing "mm" to "mmm" and when I ran the query it returned nothing.
I am so much used to SQL server throwing out error and modifying it. Oracle does not provide any error or may be our server is setup that way.
Edited by: user13101786 on May 10, 2010 12:10 PM
Edited by: user13101786 on May 10, 2010 12:10 PM
user13101786 wrote:The correct format for 'FEB' is 'M<b>ON</b>' (or 'mon', capitalization doesn't matter here), not 'M<b>MM</b>'
... I tried changing "mm" to "mmm" and when I ran the query it returned nothing.
I am so much used to SQL server throwing out error and modifying it. Oracle does not provide any error or may be our server is setup that way.Actually, Oracle throws errors when you use the wrong format (unless the string happens to be valid in the other format). I get "ORA-01821: date format not recognized" when I try to us 'MMM'. Perhaps something in your system is hiding the error messages.
1. I've been told its 10g, sorry I have limited information. If it is really critical I will ask them again. I don't use Toad or any other software, I basically created a page with VBSCRIPT that returns the fields with a random entry so that I can navigate all the tables and their rows.Couldn't you write some VBScript to query the version information? Or you could download SQL Developer free from Oracle.
For date literals I always use e.g:
This syntax only accepts YYYY-MM-DD format - the database actually converts it to the equivalent TO_DATE() expression and will reject any other format, so it's not cheating.
Doesn't VBScript let you use bind variables though?
SELECT * from table where TRUNC(user_date) > to_date('FEB-01-2010:00:00:00','mon-dd-yyyy:HH24:MI:SS') order by user_date asc.
It does return an output but it returns everything in table and does not take WHERE clause into consideration however, it does sort the date in ascending order
In that case, every date in the table is after the first of feb 2010