How to get first and last day of week in Oracle

Question

I need to get the first day and the last day of the week from some strings that have the format like this    201118    where 2011 is the year and 18 is the number of the week  Knowing the number of the week  how do I get the first and the last day of the week   How do I do this

User · Answer

cem s answer  has a flaw  if sysdate is a sunday  it returns the monday following  Inspired by his answer  here is one tested against few weeks   select       sysdate - to char sysdate-1   d     1  first day of week --A monday here from dual

User · Answer

If you are using Oracle  this code can 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

User · Answer

Unless this is a one-off data conversion  chances are you will benefit from using a calendar table   Having such a table makes it really easy to filter or aggregate data for non-standard periods in addition to regular ISO weeks  Weeks usually behave a bit differently across companies and the departments within them  As soon as you leave  ISO-land  the built-in date functions can t 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 either 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  aggregating on the fly won t make a noticable difference  but if you are performing single-row you would benefit from creating tables for the weeks as well   Using calendar tables provides a subtle performance benefit in that the optimizer can provide better estimates on static columns than on nested add months to date to char     function calls

User · Answer

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  yearweek from dual                           myconfigtable myconfigtable  where myconfigtable codpar  TYPEOFPERIOD

User · Answer

SELECT TRUNC SYSDATE  D    1ST DAY   TRUNC SYSDATE  D     6 LAST DAY FROM DUAL

User · Answer

First day of 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 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

User · Answer

Another solution is   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 must concat the cantweek with the year

User · Answer

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

User · Answer

Yet another solution  Monday is the 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

User · Answer

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

User · Answer

I think this is just a simple select statement  I hope it works  because I couldn t test it at home  because I don t have a Oracle database here  -   select to date  201118    YYYYWW    to date  201118    YYYYWW   7 from dual    You have to be carefully because there is a difference between WW and IW  Here is an article which explains the difference  http   rwijk blogspot com 2008 01 date-format-element-ww html

User · Answer

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

User · Answer

You could try this approach    Get the current date  Add the the number of years which is equal to the difference between the current date s year and the specified year  Subtract the number of days that is the last obtained date s week day s number  and it will give us the last day of some week   Add the number of weeks which is the difference between the last obtained date s week number and the specified week number  and that will yield the last day of the desired week  Subtract 6 days to obtain the first day

User · Answer

SQL gt  var P YEARWEEK varchar2 6  SQL gt  exec  P YEARWEEK     201118   PL SQL procedure successfully completed   SQL gt  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    Regards  Rob

[sql] How to get first and last day of week in Oracle?

Examples related to sql

Examples related to oracle