Adding Week End Date to calendar

Posted on 2011-10-12
Last Modified: 2012-08-29
Hello All,
I have an existing Calendar table and would like to add a week ending date column.  The week ends on Satruday.  I'm looking for help writing the SQL to update the week ending column.  I'm stuck!  This is pretty urgent for me!

Question by:hypermac
    LVL 23

    Assisted Solution

    - try this:
    select to_char(sysdate+ (7-to_char(sysdate,'D'))) endofweek from dual

    Author Comment

    More specifics might help.  I have a table with a cal_date field.  It runs thru to 2020.  It also has a day_name column that identifies the day of the week associated with the cal_date.  I want to populate a week ending column where the week ends on a Saturday.  So where cal_date = 9/1/2011 then the week ending date should be 9/3/2011 - the next saturday.  The above recommedation did not provide the results I was looking for.  More help?
    LVL 23

    Expert Comment

    - based on my earlier example, you can change sysdate to the date column. then change dual to your tablename.

    - my example below is for your date mention above:
    select to_char(to_date('09/01/2011','mm/dd/yyyy')+ (7-to_char(to_date('09/01/2011','mm/dd/yyyy'),'D'))) endofweek from dual
    LVL 6

    Accepted Solution

    You'll rather use the following function  NEXT_DAY(date,'day_of_week')

    SQL> select next_day(last_day(sysdate)-6,'SATURDAY') from dual;

    Open in new window

    instead of "sysdate", use your date column for the update...
    LVL 31

    Assisted Solution

    Assuming the datatype of the cal_date field is date -

    alter table calendar add(week_ending date);
    update calendar set week_ending =
    case when to_char(cal_date,'d') != '7' then next_day(cal_date,'SAT')
            else cal_date
    LVL 31

    Expert Comment

    Did it not work?

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to recover a database from a user managed backup

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now