Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Adding Week End Date to calendar

Posted on 2011-10-12
8
Medium Priority
?
448 Views
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!

Thanks!
0
Comment
Question by:hypermac
6 Comments
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 664 total points
ID: 36960008
- try this:
select to_char(sysdate+ (7-to_char(sysdate,'D'))) endofweek from dual
0
 

Author Comment

by:hypermac
ID: 36960085
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?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 36960151
- 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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Accepted Solution

by:
Javier Morales earned 672 total points
ID: 36960655
You'll rather use the following function  NEXT_DAY(date,'day_of_week')

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

NEXT_DAY(
---------
29-OCT-11

Open in new window


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

Assisted Solution

by:awking00
awking00 earned 664 total points
ID: 36962308
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
end;
0
 
LVL 32

Expert Comment

by:awking00
ID: 37006671
Did it not work?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

581 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