Solved

Return the Monday in current week

Posted on 2015-01-09
7
177 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 76

Accepted Solution

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

759 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

18 Experts available now in Live!

Get 1:1 Help Now