RE: Touble With Dates

But who would want to have a query only match a down-to-the-second
timestamp exactly 21 days ago from the time of the query?

If the intent of the query is to find all entries made anytime during
the day 21 days ago, you will need the trunk function.

-Charlie


-----Original Message-----
From: mark [mailto:mark@xxxxxxxxxxxxx]
Sent: Thursday, May 25, 2006 3:40 PM
To: Joseph Lamb
Cc: dbi-users@xxxxxxxx
Subject: Re: Touble With Dates


Cliff Nadler wrote:
Try the following instead:

trunc(SHIPDATE) = trunc(SYSDATE)-21

Oracle's date fields (and especially SYSDATE) are date + time. The
TRUNC will trucate the value to the date only (actually midnight) so
they can compare correctly.

It would be curious if trunc works, but there is nothing
wrong with the way Joseph was doing it -- both where clauses should
yield exactly the same results. For example:

SQL>select * from x_ship
where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY')
;

SHIPDATE
-------------------
2006-05-04 12:26:59

SQL> select * from x_ship
where trunc(shipdate) = trunc(sysdate-21);

SHIPDATE
-------------------
2006-05-04 12:26:59


I don't see any reason for your problem. You should turn on tracing and
run the query from sqlplus and from DBI and see what the db is actually
executing in each case.

I would add however that using the trunc is, IMO, preferred, because the
1) the to_char character conversion serves no purpose, and 2) it muddies
the intention. You *want* to compare *dates*, so you should compare
dates.

Mark
.

Relevant Pages

  • trunc
    ... rounding via a query. ... I couldn't find an excel equivalent for "trunc" ...
    (microsoft.public.access.queries)
  • Re: How do I use a list box to update a record in a table
    ... My intent was to make this form ... visual and easy to use for the data entry clerks. ... >>I have created a form bound to a query. ... > - that is NOT a typical manner of using a listbox! ...
    (microsoft.public.access.forms)
  • Re: Table Field Auto Update?
    ... database with two different tables for two diferent departments. ... Then create a query that concatenates the first and last name ... it's not me who's intent on using the db improperly. ... lowly newby - wannabe Access developer who's trying to do things the right ...
    (microsoft.public.access.gettingstarted)
  • Re: How do I use a list box to update a record in a table
    ... >I have created a form bound to a query. ... >automatically lists specific records based on criteria setup in the query. ... While a Listbox could be programmed to do this - with some difficulty ... understand what your intent IS. ...
    (microsoft.public.access.forms)
  • Re: Keyword Density/Count
    ... I just did a copy/paste on the bottom part of your query... ... If the intent is to summarize counts and ...
    (comp.databases.ms-sqlserver)