Hi, could somebody help me this out?
When I do
SQL> select xmlbody from messagearchive where arrivaltime = '09-DEC-00';
no rows selected
But I am sure there are data for xmlbody. I just do not remember how to deal with date here. Thanks for help.
Printable View
Hi, could somebody help me this out?
When I do
SQL> select xmlbody from messagearchive where arrivaltime = '09-DEC-00';
no rows selected
But I am sure there are data for xmlbody. I just do not remember how to deal with date here. Thanks for help.
Try this --
select xmlbody from messagearchive where to_char(arrivaltime, 'DD-MON-YY') = '09-DEC-00';
You may want to read more about to_date and to_char functions and the allowed Date formats.
- Rajeev
Thanks, Rajeev,
I tried your query, it still shows no rows selected.
Rajeev's method should work. try loosening up your criteria just to select out what the values are (E.G. "select to_char(arrivaltime, 'DD-MON-YY HH24:MI:SS') from messagearchive where arrivaltime between to_date('09-DEC-00', 'DD-MON-YY') and to_date('10-DEC-00', 'DD-MON-YY') ".
as a side issuer, you may need to create an index-based function for using to_char(arrivaltime, 'DD-MON-YY') without forcing a full table scan.
D.
Sorry, it still does not work.
I think you could try
select xmlbody from messagearchive where to_char(arrivaltime, 'DD-MM-YY') = '09-12-00';
'Mon' is kinda tricky to deal with....
To make sure you have the data you think you do, try:
select to_char(min(arrivaltime),'mm/dd/yyyy hh24:mi:ss')
from messagearchive
/
select to_char(max(arrivaltime),'mm/dd/yyyy hh24:mi:ss')
from messagearchive
/
select to_char(arrivaltime,'mm/dd/yyyy hh24:mi:ss') atime, count(*)
from messagearchive
group by to_char(arrivaltime,'mm/dd/yyyy hh24:mi:ss')
/
Thanks,Jeff,
I am sorry to you all, there is no data for 09-12-00, but 19-12-00. So the query works
select xmlbody from messagearchive where to_char(arrivaltime, 'DD-MM-YY') = '19-12-00'
Thanks to all of you for prompt response.
Minor point I feel I have to add.
Optimizationally-speaking (if that's a term), *never* put the conversion function on the column. You never want to take the date column and convert it to a string to test against a value. You will lose any possibility of using an index, assuming one had existed on that column. Even without an index, you are now performing a function call for every single row you are testing as opposed to the single call necessary to convert the constant.
Just my .02
- Chris
I think in which case.. this will work
select xmlbody from messagearchive
where trunc(to_date(arrivaltime, 'DD-MM-YY')) = trunc(to_date('19-12-00' , 'DD-MM-YY'))
Sonali
Case-in-point. This is precisely what you do *not* want to do. Never wrap the column in a function if you can at all avoid it.
- Chris