8 Replies Latest reply: May 10, 2010 6:17 PM by 445476 RSS

    Using Date in where clause

    772087
      Hello all,

      I am new to Oracle, currently using 10G + aspvbscript.

      I've been trying to query data using date in where clause but nothing seems to work.

      The column is in date format.

      It gets printed out like this: 5/1/2010 11:21:19 AM


      I tried using this query:

      SELECT * from table where TRUNC(user_date) > to_date('FEB-01-2010:00:00:00','mm-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.

      I've tried getting rid of TRUNC tried to format date in a different way but no such luck.

      Please point me to the right direction.

      Thanks.
        • 1. Re: Using Date in where clause
          Centinul
          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.
          to_date('FEB-01-2010:00:00:00','mm-dd-yyyy:HH24:MI:SS')
          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.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: Using Date in where clause
            Frank Kulash
            Hi,

            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
            ;
            • 3. Re: Using Date in where clause
              772087
              Hi Cetinul,

              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 :(

              3.

              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:
              SELECT id, name, zip, (SELECT date_start, date_end from table3 where date_id = table1.id) from table1
              Everything is fine and dandy until i put date in where clause. When i compare number to filter my query it works fine.
              SELECT * from table1 where category = 1
              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.

              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
              • 4. Re: Using Date in where clause
                Frank Kulash
                Hi,
                user13101786 wrote:
                ... I tried changing "mm" to "mmm" and when I ran the query it returned nothing.
                The correct format for 'FEB' is 'M<b>ON</b>' (or 'mon', capitalization doesn't matter here), not 'M<b>MM</b>'
                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.
                • 5. Re: Using Date in where clause
                  772087
                  Hi Frank,

                  Yeah tried using "MON" but does not return anything.

                  I am using scripts to query information rather then a client. May be our server are configured in a way not to display any errors.
                  • 6. Re: Using Date in where clause
                    William Robertson
                    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:
                    DATE '2010-05-31'
                    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?
                    • 7. Re: Using Date in where clause
                      772087
                      Thanks William,

                      Downloading SQL developer now.
                      • 8. Re: Using Date in where clause
                        445476
                        >
                        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