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')
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')
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
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')
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.