Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    59
    Provided Answers: 1

    Unanswered: Query with Oracle date column!

    Hello all,

    I have a date query question when passing the date parameters without time to the following query where startDate = '1-OCT-2011' and endDate = '31-OCT-2011':

    SELECT * FROM tableA
    WHERE recordDate >= '1-OCT-2011' and recordDate <= '31-OCT-2011';

    This above query is returned the records without the recordDate = '1-OCT-2011' and '31-OCT-2011'.

    If I used the following query, I will have all wanted records
    SELECT * FROM tableA
    WHERE recordDate > '30-SEP-2011' and recordDate < '1-NOV-2011';

    I am new to Oracle function, please help me how I can get expected records from the first query. Thanks in advance.
    Last edited by avt2k6; 05-11-12 at 10:38.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    but performance will suffer


    SELECT * FROM tableA
    WHERE trunc(recordDate) >= to_date('01-OCT-2011','DD-MON-YYYY')
    and TRUNC(recordDate) <= TO_DATE('31-OCT-2011','DD-MON-YYYY');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    This query will give you want you want. Added bonus, if you have an index on recordDate, then it will use that index.
    Code:
    SELECT * FROM tableA
    WHERE recordDate >= to_date('01-OCT-2011', 'DD-MON-YYYY') 
      and recordDate <  to_date('31-OCT-2011', 'DD-MON-YYYY')+1;
    Always specify the date format when taking date inputs as string by using the TO_DATE function.

    An Oracle DATE is actual a date and time accurate to the second. If you see a date value without a time part, that just means that the tool you are using to view the date value is not showing you the time part (and that could be for a lot of different reasons).

    You can do arithmetic on Oracle dates. This is what I used in my example above.
    DATE+1 = add one day to the given date, e.g. (25-MAY-2011)+1 = 26-MAY-2011
    DATE+(1/2) = add 12 hours to the given date, e.g. (25-MAY-2011 09h00)+(1/2) = 25-MAY-2011 21h00

    Hope this helps

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    You just need to use the oracle function(to_date) so that you are comparing a date type to a date type, for example:
    Code:
    select *
     from  schema.tableA
    where  recordDate between to_date('10-01-2011 00:00:00','mm-dd-yyyy hh24:mi:ss')
     and                      to_date('10-31-2011 23:59:59','mm-dd-yyyy hh24:mi:ss')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •