locked
Need oracle date range of previous week. RRS feed

  • Question

  • User921007156 posted

    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:MI:SS') AND to_timestamp('07-08-2012 23:59:59', 'MM-DD-YYYY HH24:MI:SS')

    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

    Wednesday, July 11, 2012 10:18 AM

Answers

  • User364663285 posted

    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:MI:SS') AND to_timestamp('07-08-2012 23:59:59', 'MM-DD-YYYY HH24:MI:SS')

    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

    Try this

    select * from test t where t.R_DATE between between (sysdate - 6 -
    case when to_char(sysdate, 'dy', 'nls_date_language=american')='sun' then 7
    when to_char(sysdate, 'dy', 'nls_date_language=american')='sat' then 6
    when to_char(sysdate, 'dy', 'nls_date_language=american')='fri' then 5
    when to_char(sysdate, 'dy', 'nls_date_language=american')='thu' then 4
    when to_char(sysdate, 'dy', 'nls_date_language=american')='wed' then 3
    when to_char(sysdate, 'dy', 'nls_date_language=american')='tue' then 2
    else 1 end
    and (sysdate -
    case when to_char(sysdate, 'dy', 'nls_date_language=american')='sun' then 7
    when to_char(sysdate, 'dy', 'nls_date_language=american')='sat' then 6
    when to_char(sysdate, 'dy', 'nls_date_language=american')='fri' then 5
    when to_char(sysdate, 'dy', 'nls_date_language=american')='thu' then 4
    when to_char(sysdate, 'dy', 'nls_date_language=american')='wed' then 3
    when to_char(sysdate, 'dy', 'nls_date_language=american')='tue' then 2
    else 1 end );

    based on the current date.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 14, 2012 10:48 AM

All replies

  • User-661350001 posted

    i have dont sql server way please change it according

    select * from test t where t.R_DATE between DateAdd(Day,-8,GetDate) and DateAdd(Day,-1,GetDate) 

    Wednesday, July 11, 2012 11:26 AM
  • User-1378764701 posted

    Check it

    SELECT *
      FROM test t
     WHERE trunc(t.R_DATE) BETWEEN trunc(sysdate-7) and trunc(sysdate)

    Friday, July 13, 2012 7:46 AM
  • User364663285 posted

    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:MI:SS') AND to_timestamp('07-08-2012 23:59:59', 'MM-DD-YYYY HH24:MI:SS')

    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

    Try this

    select * from test t where t.R_DATE between between (sysdate - 6 -
    case when to_char(sysdate, 'dy', 'nls_date_language=american')='sun' then 7
    when to_char(sysdate, 'dy', 'nls_date_language=american')='sat' then 6
    when to_char(sysdate, 'dy', 'nls_date_language=american')='fri' then 5
    when to_char(sysdate, 'dy', 'nls_date_language=american')='thu' then 4
    when to_char(sysdate, 'dy', 'nls_date_language=american')='wed' then 3
    when to_char(sysdate, 'dy', 'nls_date_language=american')='tue' then 2
    else 1 end
    and (sysdate -
    case when to_char(sysdate, 'dy', 'nls_date_language=american')='sun' then 7
    when to_char(sysdate, 'dy', 'nls_date_language=american')='sat' then 6
    when to_char(sysdate, 'dy', 'nls_date_language=american')='fri' then 5
    when to_char(sysdate, 'dy', 'nls_date_language=american')='thu' then 4
    when to_char(sysdate, 'dy', 'nls_date_language=american')='wed' then 3
    when to_char(sysdate, 'dy', 'nls_date_language=american')='tue' then 2
    else 1 end );

    based on the current date.

    Saturday, July 14, 2012 10:48 AM
  • User364663285 posted

    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:MI:SS') AND to_timestamp('07-08-2012 23:59:59', 'MM-DD-YYYY HH24:MI:SS')

    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

    Try this

    select * from test t where t.R_DATE between between (sysdate - 6 -
    case when to_char(sysdate, 'dy', 'nls_date_language=american')='sun' then 7
    when to_char(sysdate, 'dy', 'nls_date_language=american')='sat' then 6
    when to_char(sysdate, 'dy', 'nls_date_language=american')='fri' then 5
    when to_char(sysdate, 'dy', 'nls_date_language=american')='thu' then 4
    when to_char(sysdate, 'dy', 'nls_date_language=american')='wed' then 3
    when to_char(sysdate, 'dy', 'nls_date_language=american')='tue' then 2
    else 1 end
    and (sysdate -
    case when to_char(sysdate, 'dy', 'nls_date_language=american')='sun' then 7
    when to_char(sysdate, 'dy', 'nls_date_language=american')='sat' then 6
    when to_char(sysdate, 'dy', 'nls_date_language=american')='fri' then 5
    when to_char(sysdate, 'dy', 'nls_date_language=american')='thu' then 4
    when to_char(sysdate, 'dy', 'nls_date_language=american')='wed' then 3
    when to_char(sysdate, 'dy', 'nls_date_language=american')='tue' then 2
    else 1 end );

    based on the current date.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 14, 2012 10:48 AM