How to get the first and last day of the week in Oracle?

Question

I need to get the first day and last day of the week from several lines that have this format:

'201118'

where 2011 is the year and 18 is the week number. Knowing the week number, how do I get the first and last day of the week?

How to do it?

Answer 1

A week

select TRUNC(sysdate, 'iw') AS iso_week_start_date, TRUNC(sysdate, 'iw') + 7 - 1/86400 AS iso_week_end_date from dual; 

MONTH

 select TRUNC (sysdate, 'mm') AS month_start_date, LAST_DAY (TRUNC (sysdate, 'mm')) + 1 - 1/86400 AS month_end_date from dual; 

Answer 2

If you are using Oracle, this code may help you:

 select TRUNC(sysdate, 'YEAR') Start_of_the_year, TRUNC(sysdate, 'MONTH') Start_of_the_month, TRUNC(sysdate, 'DAY') start_of_the_week, TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year, TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month, TRUNC(sysdate+6, 'DAY')-1 end_of_the_week from dual; select TRUNC(sysdate, 'YEAR') Start_of_the_year, TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year, TRUNC(sysdate, 'MONTH') Start_of_the_month, TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month, TRUNC(sysdate, 'DAY')+1 start_of_the_week, -- starting Monday TRUNC(sysdate+6, 'DAY') end_of_the_week -- finish Sunday from dual; 

Answer 3

 SQL> var P_YEARWEEK varchar2(6) SQL> exec :P_YEARWEEK := '201118' PL/SQL procedure successfully completed. SQL> with t as 2 ( select substr(:P_YEARWEEK,1,4) year 3 , substr(:P_YEARWEEK,5,2) week 4 from dual 5 ) 6 select year 7 , week 8 , trunc(to_date(year,'yyyy'),'yyyy') january_1 9 , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') monday_week_1 10 , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + (week - 1) * 7 start_of_the_week 11 , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + week * 7 - 1 end_of_the_week 12 from t 13 / YEAR WE JANUARY_1 MONDAY_WEEK_1 START_OF_THE_WEEK END_OF_THE_WEEK ---- -- ------------------- ------------------- ------------------- ------------------- 2011 18 01-01-2011 00:00:00 27-12-2010 00:00:00 25-04-2011 00:00:00 01-05-2011 00:00:00 1 row selected. 

Yours faithfully,
Rob

Answer 4

 SELECT NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SUNDAY')-7 first_day_wk, NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SATURDAY') last_day_wk FROM dual 

Answer 5

Assuming you mean weeks relative to the first day of the year ...

 SELECT first_day_of_week, first_day_of_week+6 last_day_of_week FROM ( SELECT TO_DATE(YEAR||'0101','YYYYMMDD') + 7 * (week-1) first_day_of_week FROM ( SELECT substr(yearweek,1,4) YEAR, to_number(substr(yearweek,5)) week FROM ( SELECT '201118' yearweek FROM dual ) ) ) ; 

Answer 6

If this is not a one-time data conversion, most likely you will be able to use the calendar table.

Having such a table makes it easy to filter or aggregate data for non-standard periods in addition to regular ISO weeks. Weeks usually behave slightly different between companies and departments within them. Once you leave ISO-land, the built-in date functions cannot help you.

 create table calender( day date not null -- Truncated date ,iso_year_week number(6) not null -- ISO Year week (IYYYIW) ,retail_week number(6) not null -- Monday to Sunday (YYYYWW) ,purchase_week number(6) not null -- Sunday to Saturday (YYYYWW) ,primary key(day) ); 

You can create additional tables for "purchase_weeks" or "retail_weeks" or simply aggregate "on the fly":

 select a.colA ,a.colB ,b.first_day ,b.last_day from your_table_with_weeks a join (select iso_year_week ,min(day) as first_day ,max(day) as last_day from calendar group by iso_year_week ) b on(a.iso_year_week = b.iso_year_week) 

If you process a large number of records, on-the-fly aggregation will not make any noticeable difference, but if you execute one row, it is also useful for you to create tables for weeks.

Using calendar tables provides a slight performance advantage, as the optimizer can provide better estimates of static columns than with add_months(to_date(to_char())) nested calls.

Answer 7

You can try this approach:

• Get the current date.

• Add the number of years that is equal to the difference between the current date and the specified year.

• Subtract the number of days, which is the last day number with the date of the week (and this will give us the last day of the week).

• Add the number of weeks, which is the difference between the last day of the week and the specified week number, and this will result in the last day of the week.

• Cross out 6 days to get the first day.

Answer 8

Actually, I did something like this:

 select case myconfigtable.valpar when 'WEEK' then to_char(next_day(datetime-7,'Monday'),'DD/MM/YYYY')|| ' - '|| to_char(next_day(datetime,'Sunday'),'DD/MM/YYYY') when 'MONTH' then to_char(to_date(yearweek,'yyyyMM'),'DD/MM/YYYY') || ' - '|| to_char(last_day(to_date(yearweek,'yyyyMM')),'DD/MM/YYYY') else 'NA' end 

from (select to_date (YEAR || '01 ',' YYYYMM ') + 7 * (WEEK - 1) datetime, yearweek from (select substr (yearweek, 1,4) YEAR, to_number (substr (yearweek, 5)) WEEK , yearweek from (select '201018' year with two))), myconfigtable myconfigtable where myconfigtable.codpar = 'TYPEOFPERIOD'

Answer 9

Another solution (Monday - first day):

 select to_char(sysdate - to_char(sysdate, 'd') + 2, 'yyyymmdd') first_day_of_week , to_char(sysdate - to_char(sysdate, 'd') + 8, 'yyyymmdd') last_day_of_week from dual 

Answer 10

Another solution

 SELECT ROUND((TRUNC(SYSDATE) - TRUNC(SYSDATE, 'YEAR')) / 7,0) CANTWEEK, NEXT_DAY(SYSDATE, 'SUNDAY') - 7 FIRSTDAY, NEXT_DAY(SYSDATE, 'SUNDAY') - 1 LASTDAY FROM DUAL 

You have to align the cantwiecks with the year

Answer 11

@Cem's answer has a flaw, if sysdate is Sunday, it returns next Monday. Inspired by his answer, here is one tested against several weeks:

 select (sysdate - to_char(sysdate-1, 'd') + 1) first_day_of_week --A monday here from dual 

Answer 12

I think this is just a simple select statement. I hope this works because I could not check it at home, because I do not have an Oracle database here :-)

 select to_date('201118', 'YYYYWW'), to_date('201118', 'YYYYWW')+7 from dual; 

You have to be careful because there is a difference between WW and IW. Here is an article that explains the difference: http://rwijk.blogspot.com/2008/01/date-format-element-ww.html

Answer 13

First day of the week (Monday):

 SELECT TO_DATE(to_char(sysdate,'YYYY')||'0101','YYYYMMDD') + 7 * to_number(to_char(sysdate,'WW')-1)-1 first_day_week FROM dual; 

Last day of the week (Sunday):

 SELECT TO_DATE(to_char(sysdate,'YYYY')||'0101','YYYYMMDD') + 7 * to_number(to_char(sysdate,'WW')-1)+5 last_day_week FROM dual; 

Substituting your date or date field in these formulas will work for you!