7 Replies Latest reply: Feb 22, 2013 2:14 PM by 992678 RSS

    SQL Practice

    718719
      Hi, I have a question from "Oracle Database 10g: SQL Fundamentals 1", lab03_10

      Display the last name, hire date, and day of the week on which the employee started.
      Lable the column DAY. Order the results by the day of the week, starting with Monday

      --My question is how to start with Monday?

      SELECT last_name, hire_date, to_char(hire_date, 'DAY') DAY
      FROM employees
      order by Day



      Kochhar     21-ספטמבר -1989     יום חמישי
      Whalen     17-ספטמבר -1987     יום חמישי
      Grant     13-ינואר -2000     יום חמישי
      Bull     20-פברואר -1997     יום חמישי
      Geoni     03-פברואר -2000     יום חמישי
      Livingston     23-אפריל -1998     יום חמישי
      McEwen     01-אוגוסט -1996     יום חמישי
      Tucker     30-ינואר -1997     יום חמישי
      Vargas     09-יולי -1998     יום חמישי
      Seo     12-פברואר -1998     יום חמישי
      Atkinson     30-אוקטובר-1997     יום חמישי
      Landry     14-ינואר -1999     יום חמישי
      Fripp     10-אפריל -1997     יום חמישי
      Weiss     18-יולי -1996     יום חמישי
      Tobias     24-יולי -1997     יום חמישי
      Khoo     18-מאי -1995     יום חמישי
      Pataballa     05-פברואר -1998     יום חמישי
      Bell     04-פברואר -1996     יום ראשון
      Fay     17-אוגוסט -1997     יום ראשון
      Lorentz     07-פברואר -1999     יום ראשון
      Chen     28-ספטמבר -1997     יום ראשון
        • 1. Re: SQL Practice
          Rob van Wijk
          Hint: http://download.oracle.com/docs/cd/B19306_01/server.102/b14195/sqlqr07.htm#sthref1961
          • 2. Re: SQL Practice
            Frank Kulash
            Hi,

            Welcome to the forum!
            TO_CHAR (dt, 'DAY')
            is no good for sorting. It produces a VARCHAR2. If you sort by a VARCHAR2, the results are in alphabetic order. For example, the following strings are in (English) alphabetic order

            FRIDAY
            MONDAY
            SATURDAY
            SUNDAY
            THURSDAY
            TUESDAY
            WEDNESDAY

            The order is determined entirely by the letters in the strings, not by what the strings may mean.

            There's another TO_CHAR format that is good for sorting.
            TO_CHAR (dt, 'D')
            also produces a VARCHAR2, but it is one of the strings '1', '2', ..., '7', where the number represents the day of the week. What day is '1'? That depends on your NLS_TERRITORY setting. In some territories, like America and Israel, Sunday is considered to be day '1'. In other territories, like Australia and Spain, Monday is considerd to be day '1'.

            So one way to get the results you want is to change the NLS_TERRITORY setting in your session to something where Monday is '1' (assuming it is not set that way already), and order by TO_CHAR (dt, 'D'). You'll probably want to chang ethe NLS_TERRITORY setting back afterwards.
            ALTER SESSION  SET  NLS_TERRITORY = SPAIN;
            
            SELECT    hiredate
            ,         TO_CHAR (hiredate, 'DAY')  AS d
            FROM      scott.emp
            ORDER BY  TO_CHAR (hiredate, 'D');
            
            ALTER SESSION  SET  NLS_TERRITORY = AMERICA;
            Output:
            HIREDATE    TO_CHAR(H
            ----------- ---------
            28-Sep-1981 MONDAY
            09-Jun-1981 TUESDAY
            08-Sep-1981 TUESDAY
            17-Nov-1981 TUESDAY
            17-Dec-1980 WEDNESDAY
            03-Dec-1981 THURSDAY
            02-Apr-1981 THURSDAY
            03-Dec-1981 THURSDAY
            20-Feb-1981 FRIDAY
            01-May-1981 FRIDAY
            23-May-1987 SATURDAY
            23-Jan-1982 SATURDAY
            22-Feb-1981 SUNDAY
            19-Apr-1987 SUNDAY
            Notice that his is independent of the NLS_DATE_LANGUAGE. Even though I am using the Spanish territory defaults, the month abbreviations and day names still appear in the language specified by NLS_DATE_LANGUAGE, which happens to be English in my session.

            Another way TO GET THE RESULTS YOU WANT is
            ORDER BY  TRUNC (hiredate) - TRUNC (hiredate, 'IW')
            TRUNC (dt, 'IW') is the beginning of hte ISO week containing dt. ISO weeks always begin on Monday, regardless of your NLS settings, The expression above will return an integer, 0 if dt is on Monday, 1 if dt is on Tuesday, ..., 6 if dt is on Sunday.

             

            As you probably noticed, this site notmally compresses white-space. To post formatted text (such as query output copied stright from SQL*Plus) type these 6 characters:

            {code}

            (small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.
            • 3. Re: SQL Practice
              718739
              This the solution for your question.

              Please go through conversion functions.

              SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') DAY
              FROM employees
              ORDER BY DECODE(TO_CHAR(hiredate,'d'),1,8,TO_CHAR(hiredate,'d'))


              Best Regards,
              Rajam Raju.
              • 4. Re: SQL Practice
                992678
                SELECT last_name, hire_date, to_char(hire_date, 'DAY') "DAY"
                FROM employees
                ORDER BY to_char(hire_date-1, 'D')
                • 5. Re: SQL Practice
                  rp0428
                  Welcome to the forum!

                  It's nice that you want to contribute to the forums but why are you responding to a thread that is over 3 years old?

                  Please do not revive DEAD threads; let them rest in peace.

                  Focus your efforts on recent unanswered questions.
                  • 6. Re: SQL Practice
                    Paul  Horth
                    rp0428 wrote:
                    Welcome to the forum!

                    It's nice that you want to contribute to the forums but why are you responding to a thread that is over 3 years old?

                    Please do not revive DEAD threads; let them rest in peace.

                    Focus your efforts on recent unanswered questions.
                    This thread's not dead, it's merely pining for the fjords.
                    • 7. Re: SQL Practice
                      992678
                      Dear all;
                      I am Just a beginner at Oracle,working toward my first exam at OCP.
                      So sorry if my answer offend someone or let me looks like a fjords.

                      Best regards
                      A