Home » SQL & PL/SQL » SQL & PL/SQL » How To Set Thursday and Friday as Weekends in the database
How To Set Thursday and Friday as Weekends in the database [message #233918] Sat, 28 April 2007 03:30 Go to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

I need to set Thursday and Friday as weekends in my oracle 9i database. Saturday - wednesday as working days and week count starts from saturday.

Please Help
Re: How To Set Thursday and Friday as Weekends in the database [message #233957 is a reply to message #233918] Sat, 28 April 2007 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle does not know what is weekend. Oracle database works every day.

Post more precisely what you want to do.

Regards
Michel
Re: How To Set Thursday and Friday as Weekends in the database [message #233962 is a reply to message #233957] Sat, 28 April 2007 08:49 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

How do i get which week of the year i am in? there has to be a day of start and day of end of week.
Re: How To Set Thursday and Friday as Weekends in the database [message #233963 is a reply to message #233918] Sat, 28 April 2007 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The solution is application dependent & NOT directly applicable to Oracle RDBMS in general.
If your application supports makig such a change, then do so.
Otherwise, you can't make desired change.
Again, this question really has NOTHING to do with Oracle RDBMS Server Administration or anything related to Oracle s/w;
with the possible exception of Oracle Apps. software.
Re: How To Set Thursday and Friday as Weekends in the database [message #233967 is a reply to message #233963] Sat, 28 April 2007 09:24 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

see the below sql statements

SQL> select to_char(to_date('28-04-2007','DD-MM-YYYY'),'IW') from dual;

TO
--
17

SQL> select to_char(to_date('29-04-2007','DD-MM-YYYY'),'IW') from dual;

TO
--
17

SQL>
SQL> select to_char(to_date('30-04-2007','DD-MM-YYYY'),'IW') from dual;

TO
--
18

30-04-2007 is a 'MONDAY' which i database understands as start of a new week hence it is showing 18 ... what i want is the database to understand that 'SATURDAY' onwards the new week starts and not 'MONDAY'.

Is there any way?

Re: How To Set Thursday and Friday as Weekends in the database [message #233968 is a reply to message #233967] Sat, 28 April 2007 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No surely not with format IW which by definition starts on Monday.

But if you say us why you want your week start on Saturday maybe we can help you.

Regards
Michel
Re: How To Set Thursday and Friday as Weekends in the database [message #233973 is a reply to message #233918] Sat, 28 April 2007 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way?
Write your own wrapper function that returns the answer you desire; just a Small Matter Of Programming (SMOP)!
Re: How To Set Thursday and Friday as Weekends in the database [message #234009 is a reply to message #233968] Sun, 29 April 2007 00:51 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

The reason why i want the week to start on saturday is because in Kuwait, Thursday and Friday are weekends . Business week starts on Saturday.
Re: How To Set Thursday and Friday as Weekends in the database [message #234013 is a reply to message #234009] Sun, 29 April 2007 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, you have to create your own function/procedure.
Oracle only knows ISO standard and legal rules and in Kuwait weeks start on Monday, afaik.

Regards
Michel
Re: How To Set Thursday and Friday as Weekends in the database [message #234190 is a reply to message #234013] Mon, 30 April 2007 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It most certianly is possible, and it's quite easy too.
It's all done by the NLS support. Just set your NLS_TERRITORY to Kuwait, and Oracle will do it all for you.

SQL> alter session set nls_territory = 'UNITED KINGDOM';

Session altered.

SQL> select to_char(mod_date,'Day = d') from
  2  (select sysdate + level mod_date from dual connect by level <= 7)
  3  order by to_char(mod_date,'d');

TO_CHAR(MOD_D
-------------
Monday    = 1
Tuesday   = 2
Wednesday = 3
Thursday  = 4
Friday    = 5
Saturday  = 6
Sunday    = 7

7 rows selected.

SQL> alter session set nls_territory = 'KUWAIT';

Session altered.

SQL> select to_char(mod_date,'Day = d') from
  2  (select sysdate + level mod_date from dual connect by level <= 7)
  3  order by to_char(mod_date,'d')
  4  ;

TO_CHAR(MOD_D
-------------
Saturday  = 1
Sunday    = 2
Monday    = 3
Tuesday   = 4
Wednesday = 5
Thursday  = 6
Friday    = 7

7 rows selected.
Re: How To Set Thursday and Friday as Weekends in the database [message #234192 is a reply to message #234190] Mon, 30 April 2007 07:00 Go to previous message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! I mislead myself with week number:
SQL> alter session set nls_date_language=american nls_territory=KUWAIT;

Session altered.

SQL> select to_char(mod_date, 'DD-MON-YYYY "Day="Day "DayNb="D "Week="WW')
  2  from ( select to_date('27-04-2007','DD-MM-YYYY')+rownum-1 mod_date
  3         from dual       connect by level <= 11 )
  4  /
TO_CHAR(MOD_DATE,'DD-MON-YYYY"DAY="DAY"DA
-----------------------------------------
27-APR-2007 Day=Friday    DayNb=7 Week=17
28-APR-2007 Day=Saturday  DayNb=1 Week=17
29-APR-2007 Day=Sunday    DayNb=2 Week=17
30-APR-2007 Day=Monday    DayNb=3 Week=18
01-MAY-2007 Day=Tuesday   DayNb=4 Week=18
02-MAY-2007 Day=Wednesday DayNb=5 Week=18
03-MAY-2007 Day=Thursday  DayNb=6 Week=18
04-MAY-2007 Day=Friday    DayNb=7 Week=18
05-MAY-2007 Day=Saturday  DayNb=1 Week=18
06-MAY-2007 Day=Sunday    DayNb=2 Week=18
07-MAY-2007 Day=Monday    DayNb=3 Week=19

11 rows selected.

Regards
Michel

Previous Topic: Interesting Query
Next Topic: is there any function to remove white space in between words
Goto Forum:
  


Current Time: Thu Dec 08 05:53:14 CST 2016

Total time taken to generate the page: 0.10216 seconds