Home » SQL & PL/SQL » SQL & PL/SQL » order by day
order by day [message #235589] Mon, 07 May 2007 05:49 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

i want date table its has lack of dates is avaible

i want to 0/P oredring monday to sunday

how  will do that


asume that this data

HIREDATE	

2/20/1981
2/22/1981
9/28/1981
5/1/1981
6/9/1981
11/17/1981
9/8/1981
12/3/1981
1/23/1982

0/P ordering is monday to sunday












[Updated on: Mon, 07 May 2007 06:05]

Report message to a moderator

Re: order by day [message #235594 is a reply to message #235589] Mon, 07 May 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will give you a calendar:
SQL> define date_format='MM/DD/YYYY'
SQL> define from_date='04/29/2007'
SQL> define end_date='05/07/2007'
SQL> select to_char(to_date('&from_date','&date_format')+rownum-1,'&date_format') "Date"
  2  from dual
  3  connect by level <= 
  4             to_date('&end_date','&date_format')-to_date('&from_date','&date_format')
  5  /
Date
----------
04/29/2007
04/30/2007
05/01/2007
05/02/2007
05/03/2007
05/04/2007
05/05/2007
05/06/2007

8 rows selected.

Regards
Michel
Re: order by day [message #235603 is a reply to message #235594] Mon, 07 May 2007 06:45 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi Michel

this is my reqirement
i think now u got it my reqirement

SELECT hiredate, TO_CHAR(hiredate,'day') FROM EMP

this is o/p

HIREDATE	TO_CHAR(HIREDATE,'DAY')

2/20/1981	friday   
2/22/1981	sunday   
9/28/1981	monday   
5/1/1981	friday   
6/9/1981	tuesday  
11/17/1981	tuesday  
9/8/1981	tuesday  
12/3/1981	thursday 
1/23/1982	saturday 

but i want  to 

9/28/1981	monday   
6/9/1981	tuesday  
11/17/1981	tuesday  
9/8/1981	tuesday 
12/3/1981	thursday 
5/1/1981	friday 
2/20/1981	friday  
1/23/1982	saturday 
2/22/1981	sunday  




thanks
srinivas
Re: order by day [message #235607 is a reply to message #235603] Mon, 07 May 2007 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select sysdate-trunc(dbms_random.value(1,100)) dt from dual connect by level <= 1
  2  select to_char(dt,'MM/DD/YYYY') "Date", to_char(dt,'Day','NLS_DATE_LANGUAGE=''AMERICAN''') "Day
  3  from data
  4  order by to_number(to_char(dt,'D'))
  5  /
Date       Day
---------- ---------
03/12/2007 Monday
02/19/2007 Monday
03/27/2007 Tuesday
03/20/2007 Tuesday
03/22/2007 Thursday
03/15/2007 Thursday
03/29/2007 Thursday
03/17/2007 Saturday
02/24/2007 Saturday
02/11/2007 Sunday

10 rows selected.

Regards
Michel
Re: order by day [message #235785 is a reply to message #235607] Tue, 08 May 2007 00:38 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member

i want to order start monday to sunda



ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
SELECT hiredate, TO_CHAR(hiredate,'day'), TO_NUMBER(TO_CHAR(hiredate,'D'))
FROM EMP ORDER BY TO_NUMBER(TO_CHAR(hiredate,'D'))


my o/p is 

HIREDATE	TO_CHAR(HIREDATE,'DAY')	TO_NUMBER(TO_CHAR(HIREDATE,'D'))

2/22/1981	sunday   	1
9/28/1981	monday   	2
6/9/1981	tuesday  	3
11/17/1981	tuesday  	3
9/8/1981	tuesday  	3
12/3/1981	thursday 	5
2/20/1981	friday   	6
5/1/1981	friday   	6
1/23/1982	saturday 	7


Re: order by day [message #235790 is a reply to message #235785] Tue, 08 May 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are in a country where Sunday is day 1, in mine is Sunday is day 7 and Monday 1.
Just a little math to change that:
SQL> select d, d+6-7*sign(d-1)
  2  from (select rownum d from dual connect by level <= 7)
  3  /
         D D+6-7*SIGN(D-1)
---------- ---------------
         1               7
         2               1
         3               2
         4               3
         5               4
         6               5
         7               6

7 rows selected.

Regards
Michel
Re: order by day [message #235806 is a reply to message #235790] Tue, 08 May 2007 01:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The start of the week is determined by the NLS_TERRITORY.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select to_char(sysdate,'Day DD/MM/YYYY') theday
  2       , to_char(sysdate,'D') wd
  3  from   dual
  4  /

THEDAY               W
-------------------- -
Tuesday   08/05/2007 3

SQL> show parameter nls_territory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_territory                        string      AMERICA
SQL> alter session set nls_territory='FRANCE';

Session altered.

SQL> select to_char(sysdate,'Day DD/MM/YYYY') theday
  2       , to_char(sysdate,'D') wd
  3  from   dual
  4  /

THEDAY               W
-------------------- -
Tuesday   08/05/2007 2

SQL>


MHE
Re: order by day [message #235827 is a reply to message #235790] Tue, 08 May 2007 02:00 Go to previous message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi guys

i got it

this is perfect query


SELECT sri.* , CASE WHEN  num2=1  THEN 8 ELSE num2 END AS num4 FROM(
SELECT hiredate AS num, TO_CHAR(hiredate,'day') AS num1, TO_NUMBER(TO_CHAR(hiredate,'D')) AS num2
FROM EMP ORDER BY  num2) sri ORDER BY num4



NUM	NUM1	NUM2	NUM4

9/28/1981	monday   	2	2
6/9/1981	tuesday  	3	3
11/17/1981	tuesday  	3	3
9/8/1981	tuesday  	3	3
12/3/1981	thursday 	5	5
2/20/1981	friday   	6	6
5/1/1981	friday   	6	6
1/23/1982	saturday 	7	7
2/22/1981	sunday   	1	8



thanks
srinivas
Previous Topic: Parallel execution: how to.
Next Topic: How to get output like this
Goto Forum:
  


Current Time: Wed Dec 07 14:20:15 CST 2016

Total time taken to generate the page: 0.05805 seconds