Using Oracle SQL, How Does One Output Day Number of Week and Day of Week

Using Oracle SQL, how does one output day number of week and day of week?

Florin's answer is how I'd do it, but you need to be a little careful about NLS settings. The day of the week is affected by the NLS territory, so if I run this as if I'm in the US it works:

alter session set nls_territory = 'AMERICA';

select to_char(sysdate, 'D') as d, to_char(sysdate, 'Day') as day from dual;

D DAY
- ------------------------------------
6 Friday

select level as dow,
    to_char(trunc(sysdate ,'D') + level, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Monday
  2 Tuesday
  3 Wednesday
  4 Thursday
  5 Friday
  6 Saturday
  7 Sunday

But the same query run in the UK is a day off:

alter session set nls_territory = 'UNITED KINGDOM';

select to_char(sysdate, 'D') as d, to_char(sysdate, 'Day') as day from dual;

D DAY
- ------------------------------------
5 Friday

select level as dow,
    to_char(trunc(sysdate ,'D') + level, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Tuesday
  2 Wednesday
  3 Thursday
  4 Friday
  5 Saturday
  6 Sunday
  7 Monday

... and I need to adjust the calculation to correct for that:

select level as dow,
    to_char(trunc(sysdate ,'D') + level - 1, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Monday
  2 Tuesday
  3 Wednesday
  4 Thursday
  5 Friday
  6 Saturday
  7 Sunday

You can also specify the language used for the day names separately if you want:

select level as dow,
    to_char(trunc(sysdate ,'day') + level - 1, 'Day',
        'NLS_DATE_LANGUAGE=FRENCH') as day
from dual
connect by level <= 7;

DOW DAY
--- --------------------------------
  1 Lundi
  2 Mardi
  3 Mercredi
  4 Jeudi
  5 Vendredi
  6 Samedi
  7 Dimanche

Documentation for to_char() with nls_date_language and day of the week, and more in the globalisation support guide.

How to generate the first day of the week, the last day of the week and week number between two dates in Oracle

Format WW returns Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year, see Datetime Format Elements

In order to get the week number according to ISO-8601 standard use format IW. I would suggest like this:

WITH ranges AS(
    SELECT 
        DATE '2012-10-29' START_DATE,
        DATE '2016-12-31' END_DATE 
    FROM dual 
)
SELECT 
    START_DATE, END_DATE,
    TRUNC(START_DATE + 7*(LEVEL-1), 'IW') AS Week_Start_Date,
    TRUNC(START_DATE + 7*(LEVEL-1), 'IW') + 6 AS Week_End_Date,
    TO_CHAR(TRUNC(START_DATE + 7*(LEVEL-1)), 'IYYY-"W"IW') WEEK_NUMBER
FROM ranges
CONNECT BY START_DATE + 7*(LEVEL-1) <= END_DATE;

Oracle day of week (without string comparison)

You need to use to_char() to get the day number, but you can convert that to a number:

select dateSold, sum(quantity) from sales
where to_number(to_char(dateSold, 'D')) in (1, 3, 4, 7)

But D is NLS-dependent, so you'll get different results if you run this is in a session that's in, say, the USA versus on in the France. Which might be why you've seen string comparisons, as you can at least control that more:

select dateSold, sum(quantity) from sales
where to_char(dateSold, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') in ('MON', 'WED', 'THU', 'SUN')

To see the difference NLS settings make, this shows D and DY values for the same data and query as if run in the USA:

alter session set nls_territory = 'AMERICA';
alter session set nls_language = 'ENGLISH';

with cte (dateSold) as (
  select date '2018-08-01' + level - 1 from dual connect by level <= 7
)
select dateSold,
  to_number(to_char(dateSold, 'D')) as d,
  to_char(dateSold, 'DY') as dy,
  to_char(dateSold, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') as dy_english
from cte;

DATESOLD           D DY           DY_ENGLISH  
--------- ---------- ------------ ------------
01-AUG-18          4 WED          WED         
02-AUG-18          5 THU          THU         
03-AUG-18          6 FRI          FRI         
04-AUG-18          7 SAT          SAT         
05-AUG-18          1 SUN          SUN         
06-AUG-18          2 MON          MON         
07-AUG-18          3 TUE          TUE

and then as if run in France:

alter session set nls_territory = 'FRANCE';
alter session set nls_language = 'FRENCH';

with cte (dateSold) as (
  select date '2018-08-01' + level - 1 from dual connect by level <= 7
)
select dateSold,
  to_number(to_char(dateSold, 'D')) as d,
  to_char(dateSold, 'DY') as dy,
  to_char(dateSold, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') as dy_english
from cte;

and the same thing as if run in the USA:

DATESOLD          D DY               DY_ENGLISH  
-------- ---------- ---------------- ------------
01/08/18          3 MER.             WED         
02/08/18          4 JEU.             THU         
03/08/18          5 VEN.             FRI         
04/08/18          6 SAM.             SAT         
05/08/18          7 DIM.             SUN         
06/08/18          1 LUN.             MON         
07/08/18          2 MAR.             TUE

Notice that the day number and names/abbreviations are quite different, so trying to compare those against fixed values - whether in (1, 3, 4, 7) or using string-literal day names - wouldn't match reliably.

Forcing the date language to English makes the comparison safe. (Or any other language, of course - the string literals values just have to match the language you choose for the third argument to to_char().)

Get First Day Of Week From Week Number

try this:

select next_day(max(d), 'sun') requested_sun
  from (select to_date('01-01-2012', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
 where to_char(d, 'ww') = 49-1;

just set your year to_date('01-01-2012' and week number-1 49-1 as applicable.

the sunday in the 49th week of 2008?

SQL> select next_day(max(d), 'sun') requested_sun
  2    from (select to_date('01-01-2008', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
  3   where to_char(d, 'ww') = 49-1;

REQUESTED
---------
07-DEC-08

and 2012

SQL> select next_day(max(d), 'sun') requested_sun
  2    from (select to_date('01-01-2012', 'dd-mm-yyyy') + (rownum-1) d from dual connect by level <= 366)
  3   where to_char(d, 'ww') = 49-1;

REQUESTED
---------
02-DEC-12

Order date by day of the week

You can use to_char(<date>, 'd') for the day of the week:

SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') AS Day
FROM employees
ORDER BY TO_CHAR(hire_date, 'D');

There might be some additional manipulation to get the proper first day, because that depends on internationalization settings.

Using Oracle SQL, How Does One Output Day Number of Week and Day of Week