Re: Touble With Dates

Hedstrom, Charlie wrote:
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.

Nope.

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

SHIPDATE
-------------------
2006-05-04 00:01:29
2006-05-04 00:01:30
2006-05-04 00:01:31
2006-05-04 00:01:32
2006-05-04 00:01:33
2006-05-04 00:01:34
....


This query should clarify for you:

select shipdate,
to_char(shipdate, 'MMDDYY') as CHR_SHIP_DATE,
to_char(sysdate-21, 'MMDDYY') as CHR_SYSDATE_MINUS_21
from
x_ship where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY')


SHIPDATE CHR_SHIP_DATE CHR_SYSDATE_MINUS_21
------------------- ------------- --------------------
2006-05-04 00:01:29 050406 050406
2006-05-04 00:01:30 050406 050406
2006-05-04 00:01:31 050406 050406
2006-05-04 00:01:32 050406 050406
2006-05-04 00:01:33 050406 050406
2006-05-04 00:01:34 050406 050406
2006-05-04 00:01:35 050406 050406
2006-05-04 00:01:36 050406 050406


Mark
.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)