• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

oracle date between

 
deo swaroop
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Seems ok to me.
built a table xxtest :



then selected with test date between



Agad
 
deo swaroop
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic