Previous week's first and last day [message #679114] |
Thu, 06 February 2020 10:28 |
|
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 |
cookiemonster
Messages: 13925 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 #679131 is a reply to message #679119] |
Sat, 08 February 2020 10:35 |
Bill B
Messages: 1971 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
|
|
|