Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

oracle

Using Oracle SQL, how do you create a result set for:

  • Number for the Day Of The Week (1-7)
  • Name of the day (Monday, Tuesday, Wednesday, etc.)

For example:

DAY   NAME
1     Monday
2     Tuesday
3     Wednesday
4     Thursday
5     Friday
6     Saturday
7     Sunday
like image 647
Bit_hunter Avatar asked Jan 25 '13 08:01

Bit_hunter


People also ask

Can we calculate week numbers in Oracle?

How to get the week number from a date. To get the ISO week number (1-53) from a date in the column datecol , use SELECT TO_CHAR( datecol , 'IW') FROM … . To get the corresponding four-digit year, use SELECT TO_CHAR( datecol , 'IYYY') FROM … . Read more about TO_CHAR() in the Oracle manual.

How do I calculate the number of days between two dates in SQL Developer?

Just subtract the two date values. For example: SELECT SYSDATE - TO_DATE('03-02-1996','MM-DD-YYYY') FROM dual; The result is fractional in days.


1 Answers

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.

like image 66
Alex Poole Avatar answered Sep 19 '22 08:09

Alex Poole