Results 1 to 6 of 6

Thread: Select Date

  1. #1
    Join Date
    Aug 2004
    Posts
    14

    Question Unanswered: Select Date

    Hi

    could anyone advise me on the following please?

    I am trying to select records from my database where VALIDDATE is within the last 28 days of todays dates. For example today is the 6th June - I want to return all records that are valid from the 9th May to today (including the 9th May and 6th June). Any thoughts?

    Thanks

    Mark

  2. #2
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    SELECT * FROM CALENDAR_DAILY
    WHERE calendar_date BETWEEN TRUNC(SYSDATE) - 28 AND TRUNC(SYSDATE)

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    SELECT * FROM CALENDAR_DAILY
    WHERE calendar_date >= TRUNC(SYSDATE) - 28;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2004
    Posts
    14
    Thanks for these suggestions. How would I add this date query into the following selection?

    select trim (Ref_No)||'#'||trim (town)||'#'||trim (geocode1)||'#'||trim (geocode2)||'#'||trim (Validdate)||'#' from p_applics;

    Thanks again

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would you like to add the whole query, or just its WHERE clause?

    If it is only the WHERE clause, it is simple:
    Code:
    SELECT    TRIM (ref_no)
           || '#'
           || TRIM (town)
           || '#'
           || TRIM (geocode1)
           || '#'
           || TRIM (geocode2)
           || '#'
           || TRIM (validdate)
           || '#'
      FROM p_applics
     WHERE calendar_date >= TRUNC (SYSDATE) - 28;
    If there's no 'calendar_date' column in 'p_applics' table (by the way, how are we supposed to know description of your tables if you didn't provide any?), you'll have to figure that out by yourself.

    If the whole SELECT statement is to be added there, you'd do it like this:
    Code:
    SELECT    TRIM (ref_no)
           || '#'
           || TRIM (town)
           || '#'
           || TRIM (geocode1)
           || '#'
           || TRIM (geocode2)
           || '#'
           || TRIM (validdate)
           || '#'
      FROM p_applics
     WHERE validdate IN (SELECT *
                           FROM calendar_daily
                          WHERE calendar_date >= TRUNC (SYSDATE) - 28);

  6. #6
    Join Date
    Aug 2004
    Posts
    14
    Thanks - I am new to this, so am getting the hang of the info required for posted questions.

    Anyway, I tweaked the select and this appears to work:

    SELECT TRIM (ref_no)
    || '#'
    || TRIM (town)
    || '#'
    || TRIM (geocode1)
    || '#'
    || TRIM (geocode2)
    || '#'
    || TRIM (validdate)
    || '#'
    FROM p_applics
    WHERE validdate >= TRUNC (SYSDATE) - 28;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •