Path: news.easynews.com!easynews!newshosting.com!news-xfer2.atl.newshosting.com!news-out.nuthinbutnews.com!propagator-sterling!news-in.nuthinbutnews.com!usenet.INS.cwru.edu!plonk.apk.net!news.apk.net!uunet!chi.uu.net!nyc.uu.net!ash.uu.net!news.boulder.noaa.gov!not-for-mail From: Thomas Gaines Newsgroups: comp.databases.oracle.misc Subject: Re: date column select question Date: Thu, 08 Aug 2002 17:32:54 -0600 Organization: NOAA Boulder Lines: 85 Message-ID: <3D52FFA5.F7578C70@noaa.gov> References: NNTP-Posting-Host: tg2k.ngdc.noaa.gov Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="------------13E3C2926A0DC3998EF08A78" X-Trace: mwrns.noaa.gov 1028849524 12212 192.149.148.31 (8 Aug 2002 23:32:04 GMT) X-Complaints-To: usenet@news.boulder.noaa.gov NNTP-Posting-Date: Thu, 8 Aug 2002 23:32:04 +0000 (UTC) X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U) X-Accept-Language: en Xref: easynews comp.databases.oracle.misc:85348 X-Received-Date: Thu, 08 Aug 2002 16:37:13 MST (news.easynews.com) --------------13E3C2926A0DC3998EF08A78 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ralf - Yes, there is an easier way. Check out the truncate function when applied to dates. Do this little query on your end, and you should see how to apply it to your situation: select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'), to_char(trunc(sysdate),'mm/dd/yyyy hh24:mi:ss') from dual; Pretty neat, huh? You'll see that this function eliminates the time portion of the Oracle date column, allowing one to easily compare just dates and ignore times. Tom Ralph Snart wrote: > ok this is really dumb, and i would think it's also a faq, > although all the faq references i have seen only deal > with inserts and not selects. > > i've been mainly using mysql lately so my oracle knowledge > has totally dried up. > > how do i select a specific date from a date column? > > select * from table where date='05-AUG-02' > > returns nothing even though there are rows with that > date. the only way i can get it to work is to do this > horrible construction: > > select * from table where > date BETWEEN TO_DATE('08/05/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS') > AND TO_DATE('08/06/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS') > > there's got to be a simpler way... > > -rs- --------------13E3C2926A0DC3998EF08A78 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Ralf -

Yes, there is an easier way.  Check out the truncate function
when applied to dates.  Do this little query on your end, and you
should see how to apply it to your situation:

select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'),
       to_char(trunc(sysdate),'mm/dd/yyyy hh24:mi:ss') from dual;

Pretty neat, huh?  You'll see that this function eliminates the time
portion of the Oracle date column, allowing one to easily compare just
dates and ignore times.

Tom

Ralph Snart wrote:

ok this is really dumb, and i would think it's also a faq,
although all the faq references i have seen only deal
with inserts and not selects.

i've been mainly using mysql lately so my oracle knowledge
has totally dried up.

how do i select a specific date from a date column?

select * from table where date='05-AUG-02'

returns nothing even though there are rows with that
date.  the only way i can get it to work is to do this
horrible construction:

select * from table where
 date BETWEEN TO_DATE('08/05/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
  AND TO_DATE('08/06/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS')

there's got to be a simpler way...

-rs-

--------------13E3C2926A0DC3998EF08A78--