oracle date between

[deo swaroop]

I have a query:

SELECT * FROM test
WHERE created_dt >='14-MAY-10'
AND created_dt<='14-MAY-10'

returns no row...

Although there is data in the table for that particular date, the data is with the timestamp. "14-MAY-10 04:05:22"

if i query : select * from test; i am able to get the data i.e one row for 14 th may 2010.

please suggest to modify the main query in order to retrieve the result.

[Agador Paloi]

You need to convert your date string to a date to do the compare and you dont want the time in the compare.
I think you need something like this :

 
SELECT * FROM test 
 WHERE 
   trunc( created_dt)   = TO_DATE('14-MAY-10','DD-MON-yy')  
  

Agad

[raj esh]

i have a table with date column

i dont know how the data is inserting into this xx table(trans_date column,date datatype) but dates are in the format "dd/mm/yyyy hh:mm:ss" , when i gave the query like

   trans_date  between '1-apr-2010' and '5-apr-2010'  

i am getting the data from 1st to 4th only from the xx table.
but according to my book knowledge i should get the 5th date data also for my query. in the book some examples also there. but my doubt is how i am missing 5th date data for my query in xx table

[Agador Paloi]

Seems ok to me.
built a table xxtest :

   

SQL> select * from xxtest;

  TEST_SEQ TEST_DATE
---------- ---------
         1 30-MAR-10
         2 01-APR-10
         3 02-APR-10
         4 03-APR-10
         5 04-APR-10
         6 05-APR-10
         7 06-APR-10
         8 07-APR-10

8 rows selected.
 

then selected with test date between

  
SQL> select *
  2  from xxtest
  3  where test_date between '1-APR-2010' AND '5-APR-2010'
  4  /

  TEST_SEQ TEST_DATE
---------- ---------
         2 01-APR-10
         3 02-APR-10
         4 03-APR-10
         5 04-APR-10
         6 05-APR-10

SQL>

  

Agad

[deo swaroop]

I am using oracle 10G and here :

select * from txn
where txn_dt between
'30-MAY-2010' and '01-JUN-2010'

returns txns only for 30th and 31st May, it is not displaying for 1st June. why?

Also,

Is there any way to do a date search with timestamp. Like, get me all the transaction between 8AM to 10AM on 1st June2010.
we are finding it problematic, as we are using hibernate. the table column for the txn_dt is of data dype DATE.
Any suggestion ?


Thanks in advance,
Deo Swaroop

[raj esh]

while inserting into the table, given systemdate

insert into xx(date column) values (sysdate)
insert into xx(date column) values (sysdate+5)

now write the query with the dates (sysdate and sysdate+5 ) then find the result
and insert two dates directly and give the query and find the result?
now find the difference.

[deo swaroop]

Not satisfied with your asnswer.....

Is there any way to do a date search with timestamp. Like, get me all the transaction between 8AM to 10AM on 1st June2010.
we are finding it problematic, as we are using hibernate. the table column for the txn_dt is of data dype DATE.
Any suggestion ?

[Agador Paloi]

created xxtest table with this data :

 
SQL> select test_seq, TO_CHAR(test_date, ' MM/DD/YYYY HH24:MI')
  2  from
  3  xxtest
  4  /

  TEST_SEQ TO_CHAR(TEST_DATE
---------- -----------------
         1  05/30/2010 07:30
         2  05/30/2010 10:30
         3  05/31/2010 08:00
         4  05/31/2010 10:00
         5  05/31/2010 11:00
         6  06/01/2010 06:00
         7  06/01/2010 07:00
         8  06/01/2010 09:00
         9  06/01/2010 10:00
        10  06/01/2010 11:00
        11  06/01/2010 12:00

  TEST_SEQ TO_CHAR(TEST_DATE
---------- -----------------
        12  06/02/2010 05:00

12 rows selected.
 
  

selected between May 30 and June 01 as follows

  
  1  select * from xxtest
  2  where
  3*  trunc(test_date) between '30-MAY-2010' AND  '01-JUN-2010'
SQL> /

  TEST_SEQ TEST_DATE
---------- ---------
         1 30-MAY-10
         2 30-MAY-10
         3 31-MAY-10
         4 31-MAY-10
         5 31-MAY-10
         6 01-JUN-10
         7 01-JUN-10
         8 01-JUN-10
         9 01-JUN-10
        10 01-JUN-10
        11 01-JUN-10

11 rows selected.

SQL>
  

seems to work ok to me.

Remember the trunc disregards the time for the date .

To select for a time frame:

   
 1  SELECT test_seq, to_char(test_date, ' DD-MON-YYYY HH24:MI')
 2  FROM
 3       xxtest
 4  WHERE
 5       test_date between  TO_DATE('01-JUN-2010 08:00', 'DD-MON-YYYY HH24:MI')
 6                         AND     TO_DATE('01-JUN-2010 10:00', 'DD-MON-YYYY HH24:MI')
 7  /

 TEST_SEQ TO_CHAR(TEST_DATE,
--------- ------------------
        8  01-JUN-2010 09:00
        9  01-JUN-2010 10:00

 

I think when you use between without a time it implicitly uses 00:00 as the time.

Agad