Solved

Return the Monday in current week

Posted on 2015-01-09
7
193 Views
Last Modified: 2015-01-09
Guys, I am after a piece of sql that I can add to a report that depending on sysdate it will return the MONDAY of that week.

ie today is 9th January so the sysdate is 09/01/2015 the Monday is 05/01/2015

So I would like to have a sql script to show this.

Any Ideas

Thank you
0
Comment
Question by:DarrenJackson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 40540227
Try this:  trunc(sysdate,'IW')
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 40540230
select next_day(sysdate-7,'Monday') from dual

trunc  or to_char as needed
0
 

Author Comment

by:DarrenJackson
ID: 40540247
sdstuber I get an error but the example slightwv has suggested is working great.

That example seems way to simple :)  am I right in thinking any day of the week from Monday to sunday if I run it the return value will be the Monday of the current week?

Thanks for the quick response
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:DarrenJackson
ID: 40540248
Sorry sdstuber the error I get is " ora-01846 not a valid day of the week"
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40540250
>>am I right in thinking any day of the week from Monday to sunday if I run it the return value will be the Monday of the current week?


Mock up a simple test and see.  replace sysdate with sysdate-<some number> or a to_date with whatever date you want to test with.

For example:
trunc(to_date('01/01/1700','MM/DD/YYYY'),'IW')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40540260
>> ora-01846 not a valid day of the week"

Monday is a valid day, the code works for any database I tried it on.  Does your database have different nls settings?

The IW method should work regardless though,  ISO weeks are defined to start on Monday and the NLS settings are irrelevant to that.

Simple test for thousands of dates

select * from
(select trunc(sysdate + rownum,'IW') d from all_objects)
where to_char(d,'Dy') != 'Mon';

if that query returns any rows then the IW didn't work, if returns 0 rows then it worked.  Of course the 'Mon' string is subject to NLS settings though, change as needed
0
 

Author Comment

by:DarrenJackson
ID: 40540290
Guys thankyou both for the help
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

738 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