Date Format (Urgent)

[dragon99]

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.

[rsuri]

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

[dragon99]

Thanks, Rajeev,
I tried your query, it still shows no rows selected.

[DBAtrix]

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.

[dragon99]

Sorry, it still does not work.

[pando]

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....

[marist89]

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')
/

[dragon99]

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.

[chrisrlong]

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

[sonaliak]

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