CODEWITHSUNDEEP
sqldatabaseoracle-databasedatesql-order-by
Panther Gurero
Panther Gurero

Reputation: 11

Sort the list of employees by day of the week starting Monday in Oracle

I am writing general sql queries using the default scott schema in oracle. Using the emp table.

Here I want to display and sort the results on the basis of day which should start from Monday. i.e those hired on Monday should come at the top , then for tuesday and so on till sunday.

I wrote this query as : 

select * from emp order by to_char(hiredate,'DAY') ;

But the above query is displaying results in alphabetical order of the week days i.e ( People hired on Friday coming at the top , then Monday , Saturday and so on)

Kinldy help.

Upvotes: 1

Views: 982

Answers (1)

GMB
GMB

Reputation: 222402

Try :

select * from emp order by trunc(hire_date) - trunc(hire_date, 'IW')

This works by computing the number of days elapsed since the beginning of the week (starting Monday). It will return 0 for Monday and 6 for Sunday.

Why this works

trunc(hire_date) - trunc(hire_date, 'IW')

It's all in the documentation of Oracle date format specifiers :

IW : Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard. A calendar week starts on Monday.

As this specifier relies on ISO standard, it is not affected by the NLS settings of the session in which it is called (see further about the complications this avoids). This makes this approach full portable (and still quite efficient, as it consists in just two conversions).

The problem with the TO_CHAR approach

Oracle provides the TO_CHAR() function which can be used with the D format specifier to return the day of the week. As documented, this element depends on the NLS territory of the session.

The NLS_TERRITORY documentation says :

NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering.

By default, this session parameter is initialized from server's default. So to use the D specifier in a portable way, you would need to first alter your session settings.

But beware that this parameter also controls the default of other session parameters (date format, decimal separator, currency symbol, ...) : modifying it could affect other queries running in the same session in suprising ways.

Consider this demonstration

Today is Wednesday, January 28, hence the third day of the week. We are creating a new session without specifying any NLS parameter, and then :

SELECT parameter, value from v$nls_parameters WHERE parameter = 'NLS_TERRITORY';

PARAMETER     | VALUE  
:------------ | :------
NLS_TERRITORY | AMERICA

 SELECT TO_CHAR(sysdate, 'd') FROM DUAL;

 | TO_CHAR(SYSDATE,'D') |
 | :------------------- |
 | 4                    |

When using server's default territory (America), we are getting the wrong result : Oracle considers that, in America, weeks start on Sunday.

Let's alter the parameter to a territory where weeks start on Monday :

ALTER SESSION SET NLS_TERRITORY = 'FRANCE';

SELECT TO_CHAR(sysdate, 'd') FROM DUAL;

 | TO_CHAR(SYSDATE,'D') |
 | :------------------- |
 | 3                    |

Now we are getting the result we wanted... However, you should aware that, in the meantime, Oracle changed session parameter NLS_NUMERIC_CHARACTERS from ., to , : this happened because the default value of NLS_NUMERIC_CHARACTERS was implicitely changed (when NLS_TERRITORY was changed) AND no value had been explicitely for this parameter set by the client. Tricky.

Demo on DB Fiddle

NB : unlike NLS_DATE_LANGUAGE parameter, Oracle does not allow to pass NLS_TERRITORY as a third argument to TO_CHAR(I imagine that it's because it controls the defaults of other parameters).

Upvotes: 2

Related Questions