Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: Working with dates

    Hi Guys,

    My company is switching from DB2 to Oracle and I need to automate a report to oracle. I'm running the report every Monday and it will look at data from Friday 00:00:00 to Monday 03:00:00.

    The report will always on Mondays but the run time will vary based upon whenever it get release in the queue so I need to hard code the Monday 3am date.

    I cant seem to specify 3am from sysdate.

    any help would be great!
    - Using Oracle 11g with Toad v12

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understood you correctly, you want to find the "next Monday 3 AM". Here's one option (first, two examples with hardcoded dates):
    Code:
    SQL> select date '2013-08-09' + 8 - to_number(to_char(date '2013-08-09', 'D')) + 3/24 from dual;
    
    DATE'2013-08-09'+8-
    -------------------
    12.08.2013 03:00:00
    
    SQL> select date '2013-08-06' + 8 - to_number(to_char(date '2013-08-06', 'D')) + 3/24 from dual;
    
    DATE'2013-08-06'+8-
    -------------------
    12.08.2013 03:00:00
    And now, generic code (today is 09.08.2013 (dd.mm.yyyy)):
    Code:
    SQL> select trunc(sysdate) + 8 - to_number(to_char(trunc(sysdate), 'D')) + 3/24 from dual;
    
    TRUNC(SYSDATE)+8-TO
    -------------------
    12.08.2013 03:00:00
    
    SQL>
    Description:
    TRUNC(SYSDATE) removes time from SYSDATE.

    TO_CHAR(SYSDATE, 'D') returns number of today's day. As it is Friday, it returns "5" (in Croatia; might be different in other parts of the world. Pay attention to it, as you might need to adjust "8" value depending on what TO_CHAR('D') returns).

    3/24 adds 3 hours out of 24 in a day, which makes 3 AM.

  3. #3
    Join Date
    Aug 2011
    Posts
    34
    littlefoot, thanks for the response.

    code worked perfectly!

    P.S I married a croat and can't wait to visit.
    - Using Oracle 11g with Toad v12

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No kidding! Where are you / is she from, if I may ask?

  5. #5
    Join Date
    Aug 2011
    Posts
    34
    We were born in Toronto and now live in Montreal... Her Mom was born in Banja Luka and father, Zadar
    - Using Oracle 11g with Toad v12

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    That's nice! I'll be passing by Zadar tomorrow in the morning (going futher to Sibenik).

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    you could also do (assuming you are coding in english)

    next_day(trunc(sysdate)+(3/24),'MON')
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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