Home » SQL & PL/SQL » SQL & PL/SQL » Previous week's first and last day (Oracle 12)
Previous week's first and last day [message #679114] Thu, 06 February 2020 10:28 Go to next message
asagpariya
Messages: 2
Registered: February 2020
Junior Member

Hi All,

I need to get date of previous week's first and last day. For example,

today is 2/6/2020 (Thursday), I need to write a query, which gives me 01/26/2020 and 02/01/2020 as output.

if today is 2/9/2020 (Sunday) it should return me 02/02/2020 and 02/08/2020

PS. Sunday is the first day and Saturday is the last day of the week.

Can someone help me.... giving -7 to sysdate is not working as it is dynamic one.
Re: Previous week's first and last day [message #679115 is a reply to message #679114] Thu, 06 February 2020 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
to_char(<date>, 'd') gives the numeric day of the week with sunday as day 1.
So:
SQL> WITH DATA AS (SELECT SYSDATE - 30 + (ROWNUM * 3) AS source_date FROM dual CONNECT BY LEVEL <= 10)
  2  SELECT source_date,
  3  source_date - to_number(to_char(source_date, 'd')) - 6 AS start_date,
  4  source_date - to_number(to_char(source_date, 'd')) AS end_date
  5  FROM DATA
  6  
SQL> /

SOURCE_DATE START_DATE  END_DATE
----------- ----------- -----------
10/01/2020  29/12/2019  04/01/2020
13/01/2020  05/01/2020  11/01/2020
16/01/2020  05/01/2020  11/01/2020
19/01/2020  12/01/2020  18/01/2020
22/01/2020  12/01/2020  18/01/2020
25/01/2020  12/01/2020  18/01/2020
28/01/2020  19/01/2020  25/01/2020
31/01/2020  19/01/2020  25/01/2020
03/02/2020  26/01/2020  01/02/2020
06/02/2020  26/01/2020  01/02/2020

10 rows selected
Re: Previous week's first and last day [message #679116 is a reply to message #679115] Thu, 06 February 2020 10:56 Go to previous messageGo to next message
asagpariya
Messages: 2
Registered: February 2020
Junior Member
Thank you, it works.....
Re: Previous week's first and last day [message #679117 is a reply to message #679114] Thu, 06 February 2020 11:02 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
Be sure to check your NLS settings Smile
orclz> alter session set nls_territory='United Kingdom';

Session altered.

orclz> select to_char(sysdate,'d') from dual;

T
-
4

orclz> alter session set nls_territory='America';

Session altered.

orclz> select to_char(sysdate,'d') from dual;

T
-
5

orclz>
Re: Previous week's first and last day [message #679118 is a reply to message #679117] Thu, 06 February 2020 11:20 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
There was something at the back of my mind saying that was possible.
Oracle should really let you set nls_territory in to_char like it does nls_language.
Re: Previous week's first and last day [message #679119 is a reply to message #679118] Thu, 06 February 2020 14:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2948
Registered: January 2010
Location: Connecticut, USA
Senior Member
TRUNC(your_date - 6,'IW') - 1 is previous week start date for week starting Sunday.
TRUNC(your_date - 6,'IW') + 5 is previous week end date for week starting Sunday.

SY.
Re: Previous week's first and last day [message #679131 is a reply to message #679119] Sat, 08 February 2020 10:35 Go to previous message
Bill B
Messages: 1950
Registered: December 2004
Senior Member
Another way using the next_day function. This is using the using key words for the american market
WITH
    Data
    AS
        (    SELECT TRUNC (SYSDATE - 30 + (ROWNUM * 3))     AS Source_date
               FROM DUAL
         CONNECT BY LEVEL <= 10)
SELECT Source_date,
       NEXT_DAY (Source_date - 13, 'SUN')     AS Start_date,
       NEXT_DAY (Source_date - 6, 'SAT')      AS End_date
  FROM Data;

1/12/2020	1/5/2020	1/11/2020
1/15/2020	1/5/2020	1/11/2020
1/18/2020	1/5/2020	1/18/2020
1/21/2020	1/12/2020	1/18/2020
1/24/2020	1/12/2020	1/25/2020
1/27/2020	1/19/2020	1/25/2020
1/30/2020	1/19/2020	1/25/2020
2/2/2020	1/26/2020	2/1/2020
2/5/2020	1/26/2020	2/1/2020
2/8/2020	1/26/2020	2/8/2020

[Updated on: Sat, 08 February 2020 10:47]

Report message to a moderator

Previous Topic: Combining multiple regexp_like statements & correct regex syntax
Next Topic: Simultaneous DML executions on different objects in the same session
Goto Forum:
  


Current Time: Tue May 26 07:18:41 CDT 2020