Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Angry Unanswered: Need help ASAP...daterange not working in oracle

    Hello experts,

    I have a case statement as noted below
    (CASE when convert_utc_date(Actual_End_date,'MDT') between trunc(sysdate,'DAY')-7
    and trunc(sysdate,'DAY')-(1/24/60/60) then 1 else 0
    End)CurrentWeek

    Looking at this statement it should take all data in between the range
    30-apr-06 00:00:00 and 06-may-06 23:59:59.

    This is exactly what I need, I need all data returned to be within the DATE AND TIME Frame listed. So everything from date and 12:00:00 AM and date and 11:59:59 PM. AM and PM are crucial.

    When I run this I get a count returned for this value

    5/7/2006 1:49:16 AM

    Does anybody see why this is included in my count as it should not be because it is past the ending date of 06-may-06 23:59:59. Thanks everyone.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I think it's because you are converting Actual_End_date to UTC. Is "5/7/2006 1:49:16 AM" the value for Actual_End_date or for convert_utc_date(Actual_End_date,'MDT') ? If it's the value for Actual_End_date, then depending on the timezone of Actual_End_date it could be correct, could not it ? (Actual_End_date would be out of range, but convert_utc_date(Actual_End_date,'MDT') wouldn't).

    HTH & Regards,

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

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    Rbaraer,

    Thanks so much for the response because I really need the help on this. O.k your question "Is "5/7/2006 1:49:16 AM" the value for Actual_End_date". Yes it is the actual value. Is there a command to find the actual timezone in oracle for the Actual End Date. I'm getting confused on what UTC and MDT stands for. The function was in place before I started(convert_utc_date(Actual_End_date,'MDT') What is MDT? You could be right then because of what I am comparing. If Actual end date is in a different time zone then convert_utc_date(Actual_End_date,'MDT') then yes I would have a problem. Could you tell me how to check the time zone on the actual end date and what UTC and MDT stand for. Because, correct me if I am wrong, I would just need to change the MDT in the function to the time zone that Actual end date is referring to. Am I right. Your continued help is appreciated.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    Damn, you guys are the smartest. O.k the clarification really helped not only understand the issue but what the function is actually doing. My system should be/is set to EST so inorder to fix this I substituted the MDT with EST and all is well. You guys are the experts and I really appreciate the quick replies. Thanks so much and have a great day.

Posting Permissions

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