This content has been marked as final. Show 7 replies
Welcome to the forum!
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
TO_CHAR (dt, 'DAY')
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.
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'.
TO_CHAR (dt, 'D')
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;
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.
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
Another way TO GET THE RESULTS YOU WANT is
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.
ORDER BY TRUNC (hiredate) - TRUNC (hiredate, 'IW')
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:
(small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.
rp0428 wrote:This thread's not dead, it's merely pining for the fjords.
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.