Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL to sellect a date
A copy of this was sent to Brad McBride <Brad.McBride_at_Axom.com>
(if that email address didn't require changing)
On Wed, 27 Jan 1999 09:12:32 -0500, you wrote:
>Hi,
>
> I'm currently developing a database that our company will be using to
>power our internal helpdesk. The only thing that I have left to do is
>selecting tickets by date and by a date range. I tried the following
>command:
>
>SELECT * FROM tktmaster WHERE opendate=TO_DATE('01/26/99', 'MM/DD/YY');
>
>and did not get any rows back even though I knew that I should get at
>least 20 different rows. Have I done something wrong with the SQL or do
>I need to do something else in order to select specific tickets that
>were opened on a certain date.
>
>Thanks.
the opendate probably has the TIME portion on it as well. TO_DATE('01/26/99', 'MM/DD/YY') is midnight at 1/26 (and i STRONGLY urge you to use YYYY, not YY -- please)....
The 'best' way to do this (allows for indexs on opendate to be used if any) is:
SELECT *
FROM tktmaster
WHERE opendate between TO_DATE('01/26/1999', 'MM/DD/YYYY')
and to_date('01/26/1999 23:59:59', 'MM/DD/YYYY HH24:MI:SS' )/
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 27 1999 - 08:51:58 CST