Home » SQL & PL/SQL » SQL & PL/SQL » confusion for day of week (ora 9i , dev 6i)
confusion for day of week [message #288300] Sun, 16 December 2007 23:50 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts

i have one confusion in finding the day of week and i thought
monday ia a 1st day of week and sunday is the last . in that case Tuesday going to be my 1st day
for e.g
today is 17-12-2007 and its monday here. when i use this select

SQL>  select to_char(sysdate, 'Day') from dual;

TO_CHAR(S
---------
Monday

SQL>  select to_char(to_date(sysdate,'dd/mm/yyyy'),'D')  day_of_week from dual;

D
-
7


you can see the day of week is 7.... is that right ?? or i m making mistake .. ??

i want my days as follows

mon 1
tue 2
wed 3
thu 4
fri 5
sat 6
sun 7

what should i do ..? any help and suggutions


regards


Anwer
Re: confusion for day of week [message #288301 is a reply to message #288300] Mon, 17 December 2007 00:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You should use:

select to_char (sysdate, 'D') day_of_week from dual;

You should not apply a to_date to a date, because it results in an implicit conversion. Only use to_date on strings to convert them to dates.

Re: confusion for day of week [message #288302 is a reply to message #288300] Mon, 17 December 2007 00:05 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

SELECT     TO_CHAR (SYSDATE + (LEVEL - 1), 'Day') day, LEVEL day_of_week
      FROM DUAL
CONNECT BY LEVEL < 8;


DAY	DAY_OF_WEEK

Monday   	1
Tuesday  	2
Wednesday	3
Thursday 	4
Friday   	5
Saturday 	6
Sunday   	7
Re: confusion for day of week [message #288305 is a reply to message #288300] Mon, 17 December 2007 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> alter session set nls_territory=france;

Session altered.

SQL> select to_char(sysdate+level-1,'Day') day, to_char(sysdate+level-1,'D') day_of_week
  2  from dual
  3  connect by level < 8;
DAY       D
--------- -
Monday    1
Tuesday   2
Wednesday 3
Thursday  4
Friday    5
Saturday  6
Sunday    7

7 rows selected.

SQL> alter session set nls_territory=america;

Session altered.

SQL> select to_char(sysdate+level-1,'Day') day, to_char(sysdate+level-1,'D') day_of_week
  2  from dual
  3  connect by level < 8;
DAY       D
--------- -
Monday    2
Tuesday   3
Wednesday 4
Thursday  5
Friday    6
Saturday  7
Sunday    1

Day of week depends on your territory.

Regards
Michel
Re: confusion for day of week [message #288309 is a reply to message #288305] Mon, 17 December 2007 00:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The issue in this case is not getting a list of named and numeric days or the nls_territory. The original poster is not getting 1 or 2 for Monday, but 7, due to inappropriately applying to_date to a date, sysdate. Below I have demonstrated first the problem, then the correct method.

-- incorrect:
SCOTT@orcl_11g> select to_char(to_date(sysdate,'dd/mm/yyyy'),'D') day_of_week from dual;

D
-
6

-- correct:
SCOTT@orcl_11g> select to_char (sysdate, 'd') day_of_week from dual
2 /

D
-
1
Re: confusion for day of week [message #288325 is a reply to message #288309] Mon, 17 December 2007 01:49 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good eyes! Thumbs Up
I should see it. Embarassed

Regards
Michel
Previous Topic: PLS-00306 error
Next Topic: Pls. suggest the better way to select rows in this scenario
Goto Forum:
  


Current Time: Sat Dec 10 12:42:22 CST 2016

Total time taken to generate the page: 0.04845 seconds