Home » SQL & PL/SQL » SQL & PL/SQL » week start from saturday (oracle 10g, windows 7)
week start from saturday [message #683036] Sat, 28 November 2020 10:38 Go to next message
hissam78
Messages: 142
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,
Re: week start from saturday [message #683037 is a reply to message #683036] Sat, 28 November 2020 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67853
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
Re: week start from saturday [message #683039 is a reply to message #683036] Sun, 29 November 2020 01:53 Go to previous messageGo to next message
John Watson
Messages: 8547
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>
Re: week start from saturday [message #683043 is a reply to message #683036] Sun, 29 November 2020 06:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
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.
Re: week start from saturday [message #683045 is a reply to message #683043] Sun, 29 November 2020 11:13 Go to previous messageGo to next message
hissam78
Messages: 142
Registered: August 2011
Location: PAKISTAN
Senior Member
Thank you Dear Solomon Yakobson, It works fine, Stay blessed!!!
Re: week start from saturday [message #683066 is a reply to message #683037] Mon, 30 November 2020 22:48 Go to previous messageGo to next message
hissam78
Messages: 142
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear Michel Cadot,
Thankful, I have sent post using Instant SQL Formatter by today. it is really helpful to make query readable.
Re: week start from saturday [message #683070 is a reply to message #683066] Tue, 01 December 2020 00:17 Go to previous message
Michel Cadot
Messages: 67853
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you must also use code tags, read the links I posted you many times.
Once again, see Solomon's posts.

Previous Topic: How to convert JSON into XML in Oracle 10g
Next Topic: Need help to rewrite the query
Goto Forum:
  


Current Time: Tue May 18 22:49:32 CDT 2021