Re: Dumb question--between/and
Holly Gordon writes:
->
->Can someone tell me what is wrong with this between clause? Seems to cause a
->long transaction, which aborts--
->
->Select author from marc
->where creation_date between "19930901*" and "19940831*" and
->title_id in (select title_id from item where agency_id = "TNR")
->end
Holly,
No question is a dumb question...
I assume creation date is a datetime variable, but you only wish to compare
the date portion of that value. You can't use wildcards with between or really
on datetime values unless you convert it to a char type first ... but that
won't really do what you want at all efficiently.
The first solution should always work, but it may take a little longer to run
since it is converting things to a date format first:
Select author from marc
where date(creation_date) between date("9/1/93") and date("8/31/94") and
title_id in (select title_id from item where agency_id = "TNR")
end
Depending on which version (ie. which bugs you have), either of these may
also work:
Select author from marc
where date(creation_date) between "9/1/93" and "8/31/94" and
title_id in (select title_id from item where agency_id = "TNR")
end
Select author from marc
where creation_date between "9/1/93" and "8/31/94" and
title_id in (select title_id from item where agency_id = "TNR")
end
Hope this helps!
Regards,
- Cathy
--------------------------------------------------------------------------------
Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (303) 491-1294
Colorado State University Veterinary Teaching Hospital Fax: (303) 491-1205
Currently being printed by Prentice Hall, my new book (ok... my only book):
Programming INFORMIX SQL/4GL: A Step-by-Step Approach (ISBN: 0-13-149394-9)