Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Automated Date for every 2 weeks

    Hello There

    I have picked up some Oracle SQL which was originally built within a Business Objects query, where the user specified their own dates

    I now wnat to take the SQL and run it off a scheduler where no-one has to interact with it

    What I am trying to achieve is show data for the last 2 rolling weeks. It is important that there is a also a time stamp so the data picks up the rolling 2 weeks worth of data with the start date always being Midnight and the end date always being 13:00

    I have attached an example which presently returns the following error message:

    Error: ORA-01830: date format picture ends before converting entire input string
    (State1000, Native Code: 726)


    Code:

    I require help with the Between Function

    SELECT
    ENCOUNTER_WARDSTAY.WARD_STAY_START_DATE
    FROM CFIS_DATA.ENCOUNTER_WARDSTAY,
    ( SELECT level-1 AS day, 12.00 as time
    FROM dual
    CONNECT BY level <= 400
    union
    SELECT level-1 AS day, 00.00 as time
    FROM dual
    CONNECT BY level <= 400) CENSUS_POINTS
    WHERE
    (to_date(TO_CHAR(ENCOUNTER_WARDSTAY.ward_stay_STAR T_DATE + CENSUS_POINTS.DAY,'DD-MON-YYYY') || ' ' || REPLACE(TO_CHAR(CENSUS_POINTS.TIME,'00.00'),'.',': '),'DD-MON-YYYY HH24:MI')
    BETWEEN '16-JUL-2013 00:00:00' AND '31-JUL-2013 13:00:00'

    Any help or advice would be greatly appreciated

    Thanks
    Helen

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You seem to be mixing dates with strings. Stick to dates and rewrite it to something like
    Code:
    where start_date + day between to_date('16.07.2013 00:00', 'dd.mm.yyyy hh24:mi') 
          ----------------     and to_date('31.07.2013 13:00', 'dd.mm.yyyy hh24:mi')
                 ^                 -------------------------------------------------
                 |                                  ^
           this is a DATE                           |
                                            these are DATEs as well

Posting Permissions

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