How to Get First and Last Day of Week in Oracle

How to generate the first day of the week, the last day of the week and week number between two dates in Oracle

Format WW returns Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year, see Datetime Format Elements

In order to get the week number according to ISO-8601 standard use format IW. I would suggest like this:

WITH ranges AS(
    SELECT 
        DATE '2012-10-29' START_DATE,
        DATE '2016-12-31' END_DATE 
    FROM dual 
)
SELECT 
    START_DATE, END_DATE,
    TRUNC(START_DATE + 7*(LEVEL-1), 'IW') AS Week_Start_Date,
    TRUNC(START_DATE + 7*(LEVEL-1), 'IW') + 6 AS Week_End_Date,
    TO_CHAR(TRUNC(START_DATE + 7*(LEVEL-1)), 'IYYY-"W"IW') WEEK_NUMBER
FROM ranges
CONNECT BY START_DATE + 7*(LEVEL-1) <= END_DATE;

how to find start date and end date of all weeks present in a month in oracle

Try this. Add more months in m table, or replace it with your actual table.
I am assuming that MAR-17 is in varchar data type. If not, replace to_date(mon,'MON-YY') with mon in the code everywhere.

SELECT mon,
       TRUNC(to_date(mon,'MON-YY'), 'iw') + nxt AS iso_week_start_date ,
       TRUNC(to_date(mon,'MON-YY'), 'iw') + 6 + nxt AS iso_week_end_date
FROM
  (SELECT LEVEL*7 - 7 AS nxt
   FROM dual CONNECT BY LEVEL <=6)
CROSS JOIN
  (SELECT 'MAR-17' AS mon
   FROM dual
   UNION ALL SELECT 'APR-17'
   FROM dual
   UNION ALL SELECT 'MAY-17'
   FROM dual
   ) m --Sample data. Replace with original table and change column mon to your `char` column.
WHERE --where clause to filter out of month dates.
    to_date(mon,'MON-YY') <= TRUNC(to_date(mon,'MON-YY'), 'iw') + nxt --iso_week_start_date
  AND last_day(to_date(mon,'MON-YY')) >= TRUNC(to_date(mon,'MON-YY'), 'iw') + nxt --iso_week_start_date
ORDER BY 2

Explanation:

select TRUNC (sysdate, 'IW') from dual
Returns first day of the week (monday) of the given date.
(select level*7 - 7 as nxt from dual connect by level <=6) It will
geneate column in range of 0,7,14,21,28,35 .Later cross join it to multiply each month 6 times to generate 6 different weeks.
Utilize this while adding days to the fist week.
Where clause is used to filter out of boundry dates.

Get First Day Of Week From Week Number

try this:

select next_day(max(d), 'sun') requested_sun
  from (select to_date('01-01-2012', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
 where to_char(d, 'ww') = 49-1;

just set your year to_date('01-01-2012' and week number-1 49-1 as applicable.

the sunday in the 49th week of 2008?

SQL> select next_day(max(d), 'sun') requested_sun
  2    from (select to_date('01-01-2008', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
  3   where to_char(d, 'ww') = 49-1;

REQUESTED
---------
07-DEC-08

and 2012

SQL> select next_day(max(d), 'sun') requested_sun
  2    from (select to_date('01-01-2012', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
  3   where to_char(d, 'ww') = 49-1;

REQUESTED
---------
02-DEC-12

SQL Function to select first day of current year and everyday after

Are you looking for something like this:

SELECT
    ST_DT + LEVEL - 1,
    TO_CHAR(ST_DT + LEVEL - 1, 'IW') AS DY
FROM
    (
        SELECT
            TRUNC(SYSDATE, 'YEAR') ST_DT,
            TRUNC(SYSDATE, 'YEAR') + INTERVAL '12' MONTH - INTERVAL '1' DAY END_DT
        FROM DUAL
    )
CONNECT BY LEVEL <= END_DT - ST_DT
ORDER BY LEVEL

Cheers!!

Getting a date for start of the week, from week and year numbers on Oracle SQL

ISO-8601 counts the first week of the year as the first week which has the majority of its days in that year. To have the majority of its days in that year then it must contain at least 4 days of the week within that year and must contain the 4th January.

You can use this to work out the start of the first iso-week as:

TO_DATE( year, 'YYYY' ) will give 1st January;
Then you can add 3 days to get to the 4th January; and
Use TRUNC( date_value, 'IW' ) to get the Monday of the 1st iso-week of the year
Then just add the correct offset of weeks to go from the 1st to Nth week.

Test Data:

CREATE TABLE test_data ( Week, Year, Sales, Visits ) AS
SELECT '32', 2017, 22,  55 FROM DUAL UNION ALL
SELECT '33', 2017, 30,  65 FROM DUAL UNION ALL
SELECT '01', 2019, 55, 103 FROM DUAL;

Query:

SELECT t.*,
       TRUNC( TO_DATE( year, 'YYYY' ) + 3, 'IW' ) + 7 * ( week - 1 ) AS week_start
FROM   test_data t

Output:


WEEK | YEAR | SALES | VISITS | WEEK_START
:--- | ---: | ----: | -----: | :---------
32   | 2017 |    22 |     55 | 07-AUG-17 
33   | 2017 |    30 |     65 | 14-AUG-17 
01   | 2019 |    55 |    103 | 31-DEC-18

db<>fiddle here

Get first and last day of month into variables - Oracle

You can use such a query to detect first and last days of the current month :

SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1,
       TRUNC(LAST_DAY(SYSDATE)) 
  INTO :startDate,:endDate 
  FROM DUAL;

with the contribution of ADD_MONTHS() function

Update : Alternatively use this PL/SQL code block :

DECLARE
  startDate date;
  endDate   date;
BEGIN
 startDate := TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1;
 endDate   := TRUNC(LAST_DAY(SYSDATE)); 
 DBMS_OUTPUT.PUT_LINE ('startDate :  '||startDate); 
 DBMS_OUTPUT.PUT_LINE ('endDate :  '||endDate);  
END;
/

Demo

PLSQL - How to find Monday and Friday of the week of a given date

Assuming that your column is of type date, you can use trunc to get the first day of the week (monday) and then add 4 days to get the friday.

For example:

with yourTable(d) as (select sysdate from dual)
select trunc(d, 'iw'), trunc(d, 'iw') + 4
from yourTable

To format the date as a string in the needed format, you can use to_char; for example:

with yourTable(d) as (select sysdate from dual)
select to_char(trunc(d, 'iw'), 'dd/mm/yy') ||'-'|| to_char(trunc(d, 'iw') + 4, 'dd/mm/yy')
from yourTable

gives

15/01/2018-19/01/18