Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    25

    Question Unanswered: Need oracle date range of previous week.

    Hi All,

    I have to run a query every Monday, that only pulls the results of the previous monday - sunday. Today I'm currently doing this manually like the following...


    select * from test t where t.R_DATE between to_timestamp('07-02-2012 12:00:00', 'MM-DD-YYYY HH24:MIS') AND to_timestamp('07-08-2012 23:59:59', 'MM-DD-YYYY HH24:MIS')

    How can I run this query without having to put the dates in manually?


    Thanks!
    Matt
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

  2. #2
    Join Date
    Jul 2010
    Posts
    24
    Have you tried using condition
    Code:
     between trunc(sysdate)-7  and trunc(sysdate)
    ??

  3. #3
    Join Date
    Oct 2007
    Posts
    25

    Question

    Quote Originally Posted by sunilmp View Post
    Have you tried using condition
    Code:
     between trunc(sysdate)-7  and trunc(sysdate)
    ??
    Would this also include the current monday?

    Thanks.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    adjust as required
    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
    Oct 2007
    Posts
    25
    I guess this would work, if ran on a monday.

    between trunc(sysdate)-7 and trunc(sysdate)-1

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >between trunc(sysdate)-7 and trunc(sysdate)-1
    How many days exist within range above? You do the math.
    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
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If couldn't ran on a monday by some reason, and need to ran on anoher day.

    between trunc(sysdate , IW)-?/*adjust as required*/ and trunc(sysdate, IW)-?/*adjust as required*/

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    just apply a small matter of programming or logic
    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.

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Post

    Quote Originally Posted by tonkuma View Post
    If couldn't ran on a monday by some reason, and need to ran on anoher day.

    between trunc(sysdate , IW)-?/*adjust as required*/ and trunc(sysdate, IW)-?/*adjust as required*/
    The answer will still be trunc(sysdate, 'IW')-7.

    Remember that no matter what day today is, the trunc(sysdate, 'IW') will always return the Monday of this week.

    So, if today is Thursday 12th July 2012, then trunc(sysdate, 'IW') will return Monday 9th July 2012. And guess what... If we run it tomorrow, i.e. Friday 13th (oooohhhh) July 2012, the trunc(sysdate, 'IW') will still return Monday 9th July 2012.

    Another thing to remember is that trunc(sysdate, 'IW') will remove the time portion of the date. So the full result of running trunc(sysdate, 'IW') is actually "Monday 9th July 2012 00:00:00". Note that the time portion is zero no matter what time I run the trunc(sysdate, 'IW').

    What does that mean for our where clause?
    Well what that means is that I should not be using a BETWEEN clause, but rather a >= and <.
    Consider the following:
    Code:
    select * from test t 
     where t.R_DATE between trunc(sysdate , 'IW')- 7 
                        and trunc(sysdate, 'IW')
    That would mean select all columns from test table where the R_DATE value is between "Monday 2nd July 2012 00:00:00" and "Monday 9th July 2012 00:00:00"; and BETWEEN is inclusive.

    But what happens if I have a row with an R_DATE = "Monday 9th July 2012 00:00:00"? Well, that row will be selected when I run the query now (i.e. "Thursday 12th July 2012 12:29:53"). But should it? I don't think so. That row actually belongs to this weeks data, not last weeks data. So the a more accurate query would be:
    Code:
    select * from test t 
     where t.R_DATE >= trunc(sysdate , 'IW')- 7 
       and t.R_DATE <  trunc(sysdate, 'IW')

Posting Permissions

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