OraFAQ Forum: SQL & PL/SQL » week start from saturday

Home » SQL & PL/SQL » SQL & PL/SQL » week start from saturday (oracle 10g, windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

week start from saturday [message #683036]

Sat, 28 November 2020 10:38

hissam78
Messages: 186
Registered: August 2011
Location: PAKISTAN

Senior Member

Dear Experts,
it is requested you that
i have used the following statement to show the start and end day of the week

Select
trunc(sysdate,'D') START_DATE,
trunc(sysdate,'D')+6 END_DATE
from dual

but it start week from Sunday to Saturday,
we need to start week from Saturday to end day Friday,
what change we need to make in this query so that week day start from Saturday and end day of the week should be Friday?

Thankful,

Report message to a moderator

Re: week start from saturday [message #683037 is a reply to message #683036]

Sat, 28 November 2020 11:02

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 24 November 2020 17:06

Michel Cadot wrote on Thu, 08 October 2020 10:49
You also forgot to format your post and to put INSIDE the post the output you want with the rules to apply to get it.
AS ALWAYS you refuse to follow the rules.

Quote:
Is it possible to generate output or i need to send again..
The later is what to do.
I repeat once more:

Michel Cadot wrote on Tue, 08 October 2019 13:08

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Michel Cadot wrote on Tue, 24 November 2020 17:43

hissam78 wrote on Tue, 24 November 2020 17:16
Dear Michel Cadot
my apologies, i have tried a lot to work out as per your given instructions but "Instant Sql formatter" opening the following site,
http://www.dpriver.com/pp/sqlformat.htm

but "This site can't be reached" message is showing

Thanks,
This site is CURRENTLY unreachable and this does not prevent you from using code tags.
Do you think Solomon used the site?

The site is currently accessible and you can use code tags.

[Updated on: Sat, 28 November 2020 11:04]

Report message to a moderator

Re: week start from saturday [message #683038 is a reply to message #683037]

Sat, 28 November 2020 11:24

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> ...
  2  /

SQL> Select trunc(sysdate,'D') START_DATE, trunc(sysdate,'D')+6 END_DATE from dual;
START_DA END_DATE
-------- --------
28/11/20 04/12/20

Report message to a moderator

Re: week start from saturday [message #683039 is a reply to message #683036]

Sun, 29 November 2020 01:53

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

Are you tryng to make an application geographically aware? For example,

orclz>
orclz> alter session set nls_territory='Afghanistan';

Session altered.

orclz> select to_char(sysdate,'D') from dual;

T
-
2

orclz> alter session set nls_territory='Japan';

Session altered.

orclz> select to_char(sysdate,'D') from dual;

T
-
1

orclz> alter session set nls_territory='Germany';

Session altered.

orclz> select to_char(sysdate,'D') from dual;

T
-
7

orclz>

Report message to a moderator

Re: week start from saturday [message #683043 is a reply to message #683036]

Sun, 29 November 2020 06:45

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

Date format element D is NLS dependent therefore trunc(sysdate,'D') will produce different results for different clients if week starts on a different day in that client's territory. If you want to get Saturday to Sunday regardless of what client territory is use ISO format IW:

select  trunc(sysdate + 2,'iw') - 2 week_start_day,
        trunc(sysdate + 2,'iw') + 4 week_end_day
  from  dual
/

For example:

SQL> alter session set nls_territory=america;

Session altered.

SQL> select  trunc(sysdate + 2,'iw') - 2 week_start_day,
  2          trunc(sysdate + 2,'iw') + 4 week_end_day
  3    from  dual
  4  /

WEEK_STAR WEEK_END_
--------- ---------
28-NOV-20 04-DEC-20

SQL> alter session set nls_territory=sweden;

Session altered.

SQL> select  trunc(sysdate + 2,'iw') - 2 week_start_day,
  2          trunc(sysdate + 2,'iw') + 4 week_end_day
  3    from  dual
  4  /

WEEK_START WEEK_END_D
---------- ----------
2020-11-28 2020-12-04

SQL> alter session set nls_territory=syria;

Session altered.

SQL> select  trunc(sysdate + 2,'iw') - 2 week_start_day,
  2          trunc(sysdate + 2,'iw') + 4 week_end_day
  3    from  dual
  4  /

WEEK_STA WEEK_END
-------- --------
28/11/20 04/12/20

SQL>

SY.

Report message to a moderator