Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Red face Unanswered: Date / Hour issue

    I am having a problem with trying to get dates to work consistantly when I enter the hour. I have a requirement to look back at critical service cases that were created since 5 am yesterday. This code works during the day but for some reason stops working after 5pm each day. The data instance is built every night so it does not change at all during the day.
    Ideas?

    > TRUNC(SYSDATE) - 1 + 5 / 24

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    What do you mean by "stops working"?
    And what code stops working? Can you provide an example?


    .

  3. #3
    Join Date
    Dec 2005
    Posts
    5
    The report works all day long. If their were 5 Critical cases opened since 5am the prior day then the Report shows those cases when you run it at 7am , or 8am or 10 am , or 2pm or 4pm. However when you run the report after 5PM the report shows no cases.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This code works during the day
    Code? What code?
    How/why do you expect us (TINU) to fix your code when you refuse to show it?
    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2005
    Posts
    5
    The Only Part That Is Causing The Problem Is Stated On The First Post. Its A Date Thing. The Following Statement Is The Problem.

    *********************************
    > Trunc(sysdate) - 1 + 5 / 24)
    *******************************

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The Following Statement Is The Problem. > Trunc(sysdate) - 1 + 5 / 24)
    Then change it until it works for you.
    YOYO!
    P.S.
    Ever stop to consider that if your problem assessment was 100% correct,
    then you'd likely not need any additional assistance?

    =========================================
    If reality is not as it "should be", then maybe it isn't as it "should be".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How does your query look like? In such an example, it seems that the code is OK:
    Code:
    SQL> select sysdate from dual;
    
    SYSDATE
    --------
    19.12.05
    
    SQL> select to_char(vri, 'dd.mm.yyyy. hh24:mi') vri from brisime order by vri;
    
    VRI
    -----------------
    18.12.2005. 04:00
    18.12.2005. 05:30
    18.12.2005. 19:30
    18.12.2005. 23:30
    19.12.2005. 16:00
    19.12.2005. 17:10
    19.12.2005. 23:30
    
    7 rows selected.
    
    SQL> select to_char(vri, 'dd.mm.yyyy. hh24:mi') vri
      2  from brisime
      3  where vri > trunc(sysdate) - 1 + 5/24
      4  order by vri;
    
    VRI
    -----------------
    18.12.2005. 05:30
    18.12.2005. 19:30
    18.12.2005. 23:30
    19.12.2005. 16:00
    19.12.2005. 17:10
    19.12.2005. 23:30
    
    6 rows selected.
    
    SQL>
    The latter query fetches 6 records as it should (if I understood your question well). Now, where's the problem? It would really help if you posted your actual code, not only its fragments.

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Zeppelin!
    he Following Statement Is The Problem.

    > Trunc(sysdate) - 1 + 5 / 24)
    That's not a statement.

  9. #9
    Join Date
    Dec 2005
    Posts
    5

    here is the query

    SELECT INC_ID, 'INC' AS CASETYPE, HPSD_QUERY.INCIDENT.STATUS, PRIORITY, CREATED, CI_NAME, SOLUTION, DESCRIPTION, INFORMATION, NAME2
    FROM HPSD_QUERY.INCIDENT, HPSD_QUERY.CONFIGURATION_ITEM
    WHERE HPSD_QUERY.INCIDENT.CI_OID = HPSD_QUERY.CONFIGURATION_ITEM.CIT_OID (+) AND (PRIORITY = 'Critical' OR
    PRIORITY = 'Urgent') AND (CREATED > TRUNC(SYSDATE) - 1 + 5 / 24)
    UNION ALL
    SELECT SC_ID, 'SVC' AS CASETYPE, HPSD_QUERY.SERVICE_CALL.STATUS, PRIORITY, CREATED, CI_NAME, SOLUTION, DESCRIPTION, INFORMATION, NAME2
    FROM HPSD_QUERY.SERVICE_CALL, HPSD_QUERY.CONFIGURATION_ITEM
    WHERE HPSD_QUERY.SERVICE_CALL.CI_OID = HPSD_QUERY.CONFIGURATION_ITEM.CIT_OID (+) AND (PRIORITY = 'Critical' OR PRIORITY = 'Urgent') AND (CREATED > TRUNC(SYSDATE) - 1 + 5 / 24)

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What is stored in "CREATED". Is it SYSDATE or your own calculated timestamp?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    What is the datatype of CREATED?

    I assume similar columns exist in both INCIDENT and SERVICE_CALL, although you don't say.

  12. #12
    Join Date
    Dec 2005
    Posts
    5
    "CREATED" is the field name for the date out of history of when the case was created.

    Results are a standard date format. Perhaps I need to shift this to military time?

    Example of current data: 10/20/2005 7:40:46 AM

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    NEVER EVER store a date in a varchar2 field. ONly store it in a date field.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  14. #14
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Zeppelin!
    Results are a standard date format. Perhaps I need to shift this to military time?
    The database does not know what standard you chose. This is why it has a DATE datatype.

  15. #15
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    1. As WilliamR and beilstwh said : always use DATE (or TIMESTAMP) datatype to store a date, NEVER a Varchar2.

    2. If you want to compare data of different datatypes, then ALWAYS convert data explicitly, never rely on automatic convertion. This is especially true for dates.

    All in all : either follow point 1 and change your "CREATED" column to a DATE column (create a new DATE column CREATED_TMP, put TO_DATE(CREATED,'MM/DD/YYYY HH:MIS AM') inside it, drop the old column and then rename CREATED_TMP to CREATED) or, at least, follow point 2 and put TO_DATE(CREATED, 'MM/DD/YYYY HH:MIS AM') in your query instead of CREATED.

    HTH & Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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