Finding the first day of the current week 2

[MikeJones]

Hi Everyone,

I need to work out the date for the start of the current week, assuming that Monday is the first day, so If I ran the query today (19th) it would return today, but If I ran it yesterday (18th) it would return the 12th.

How can I do this in SQL??

Cheers,

Mike.

 

[sem]

Use

trunc(<your_date>,'WW')

 

[lewisp]

If Monday is the first day, then use

select to_date(sysdate - mod(to_number(to_char(sysdate,'j')),7))
from dual;

 

[karluk]

I think the easiest way to do this is with the NEXT_DAY function. Subtract 7 days to get to a week ago and then use next_day to move forward to the next Monday.

select next_day(sysdate-7, 'Monday') from dual;

You can truncate the result if you need to get rid of hours and minutes.

 

[MikeJones]

I've gone with sem's in the end. Just incase anywone has a better way of doing it here is the problem and what I coded...

I had to find records that were due in the current week, the week is defined as running from midnight Monday to end of Sunday, the table has a column called due_on which is a date with the time element so I have coded...

due_on between trunc(sysdate,'WW') and trunc(sysdate,'WW')+7 - GC_ONE_SECOND

GC_ONE_SECOND is a number constant defined as 1 /25 /60 /60 just to make the code easier to read.

Any takers for a neater solution?

Mike.

 

[sem]

trunc(due_on,'WW') = trunc(sysdate,'WW')

if you have NO INDEX on due_on you're planing to use.

 

[sem]

I do apologise for misleading you, I always mix them up, 'WW' returns the first-day-of-yer week.

You need trunc(sysdate, 'D')

You should also be aware of nls-dependency of trunc function:

SQL> alter session set nls_territory='AMERICA';
SQL> select trunc(sysdate,'D') from dual;

TRUNC(SYS
---------
18-NOV-01
SQL> alter session set nls_territory='UKRAINE'
SQL> select trunc(sysdate,'D') from dual;

TRUNC(SYSD
----------
19.11.2001

 

[MikeJones]

OK, now I'm confused...

select trunc(sysdate,'D'), trunc(sysdate,'WW') from dual

returns the same results, under what conditions would they differ?

Thnaks in advance,

Mike.

 

[sem]

They return the same result because January 1, 2001 is Monday! Check for select trunc(sysdate-366,'D'), trunc(sysdate-366,'WW') and even more descriptive:

SQL> select to_char(to_date('01.01.00','dd.mm.rr'),'DAY') from dual;

TO_CHAR(T
---------
SATURDAY

SQL> select to_char(trunc(sysdate-366,'WW'),'DAY') from dual;

TO_CHAR(T
---------
SATURDAY

SQL> select to_char(trunc(sysdate-366,'D'),'DAY') from dual;

TO_CHAR(T
---------
MONDAY

 

[karluk]

Sem's right that the 'WW' format is completely unreliable for what you want. Whether it works or not depends on the year.

The 'D' format is the way to go, but you need to add a day to get to Monday. In most countries the week starts on Sunday.

select trunc(sysdate,'D')+1 from dual;

I still prefer my way because it's more obvious what's happening, but I must admit that Sem's function is simpler.

 

[sem]

Karluk is also right but only partly: adding 1 day to get a Monday depends on your NLS settings, namely NLS_TERRITORY, because in my country week already starts from Monday:

SQL> ALTER SESSION SET NLS_TERRITORY='AMERICA';
SQL> select to_char(trunc(sysdate,'D'),'DAY') from dual;

TO_CHAR(T
---------
SUNDAY
SQL> ALTER SESSION SET NLS_TERRITORY='UKRAINE';
SQL> select to_char(trunc(sysdate,'D'),'DAY') from dual;

TO_CHAR(T
---------
MONDAY

 

[MikeJones]

why is Oracle NEVER simple... I have to get the date back for Monday, regardless of which Country it is running in and therefore independent of any NLS Settings, which if I've read the obove correctly means sems suggestion is never going to work as the below shows.

SQL> ALTER SESSION SET NLS_TERRITORY='AMERICA';
SQL> select to_char(trunc(sysdate,'D'),'DAY') from dual;

TO_CHAR(T
---------
SUNDAY
SQL> ALTER SESSION SET NLS_TERRITORY='UKRAINE';
SQL> select to_char(trunc(sysdate,'D'),'DAY') from dual;

TO_CHAR(T
---------
MONDAY

I'll have to swap to Karluk's suggestion, well thanks for the ideas!!!

Cheers,

Mike.

 

[sem]

In any case you should pass the NAME of the day so this decision also depends on NLS.

 

[karluk]

This is turning into an unexpectedly complicated problem. Here is an attempt to make the function independent of NLS settings.

select next_day(sysdate-7,to_char(to_date('01-jan-01','dd-mon-yy'),'day')) from dual;

The strategy is similar to my original suggestion, but I've replaced the literal &quot;Monday&quot; with an automatic generation of the week day. I use the date 01-jan-01 for this calculation because I know it falls on a Monday.

No one would call this a simple function, but I hope it makes the calculation independent of the environment in which it runs.

 

[sem]

Well done, Karluk!

 

[karluk]

I'm afraid that my previous suggestion isn't quite right. It's important to avoid abbreviations like &quot;Jan&quot; which aren't the same in every language. Still, this is easily corrected. Try

select next_day(sysdate-7,to_char(to_date('01-01-01','dd-mm-yy'),'day')) from dual;

 

[sem]

Ok, I've catched the idea, using jan is only details

 

[MikeJones]

Thanks for the solution, it's working a treat, I'll have to remember these Date settings, they're something of a gotcha...