aspose file tools*
The moose likes Oracle/OAS and the fly likes oracle date between Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "oracle date between " Watch "oracle date between " New topic
Author

oracle date between

deo swaroop
Ranch Hand

Joined: May 21, 2008
Posts: 30
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
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
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 :


Agad
raj esh
Greenhorn

Joined: Apr 03, 2010
Posts: 10
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
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
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
Seems ok to me.
built a table xxtest :



then selected with test date between



Agad
deo swaroop
Ranch Hand

Joined: May 21, 2008
Posts: 30
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
Greenhorn

Joined: Apr 03, 2010
Posts: 10
while inserting into the table, given systemdate


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
Ranch Hand

Joined: May 21, 2008
Posts: 30
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
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
created xxtest table with this data :



selected between May 30 and June 01 as follows



seems to work ok to me.

Remember the trunc disregards the time for the date .

To select for a time frame:



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

Agad
 
 
subject: oracle date between