Solved

Return the Monday in current week

Posted on 2015-01-09
7
186 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-12560: TNS:protocol adapter error 8 133
Oracle Listener Not Starting 11 44
dbms_crypto.decrypt   errors out 6 31
Oracle Insert not working 10 28
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

831 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