TO_CHAR Day Format Masks

View All Scripts Login to Run Script

Script Name TO_CHAR Day Format Masks
Description Examples of how you can use to_char to extract day-of-week from a date.
Area SQL General
Contributor Chris Saxon (Oracle)
Created Thursday March 01, 2018

Statement 1

alter session set nls_date_format = '  DD-Mon-YYYY  '

Statement processed.

Statement 2

The DAY format gives you the full spelling of the day, DY the three letter abbreviation and D the day-of-week number (1-7).

select to_char(date'2018-01-01', 'DAY') full_name,  
       to_char(date'2018-01-01', 'DY') abbreviation,  
       to_char(date'2018-01-01', 'D') day_of_week  
from   dual


FULL_NAME ABBREVIATION DAY_OF_WEEK
MONDAY   MON 2

FULL_NAME	ABBREVIATION	DAY_OF_WEEK
MONDAY	MON	2

Statement 3

The case of the format controls the case of the output.

select to_char(date'2018-01-01', 'Day') initcap,  
       to_char(date'2018-01-01', 'day') lower,  
       to_char(date'2018-01-01', 'DAY') upper  
from   dual


INITCAP LOWER UPPER
Monday   monday   MONDAY

INITCAP	LOWER	UPPER
Monday	monday	MONDAY

Statement 4

alter session set nls_date_language = SPANISH

Statement processed.

Statement 5
You can return day names in different languages. This inherits the setting from the client's nls_date_language. Clients can set their own language, so this can lead to unpredictable behaviour!
```
select to_char(date'2018-01-01', 'Day') dy   
from   dual
```
```
DY
Lunes    
```

DY
Lunes

Statement 6

alter session set nls_date_language = ENGLISH

Statement processed.

Statement 7

select to_char(date'2018-01-01', 'Day') dy 
from   dual


DY
Monday

Statement 8
You can state the language for the day using the third parameter of to_char. This avoids problems that can occur due to different clients changing their NLS parameters.
```
select to_char(date'2018-01-01', 'fmDay', 'NLS_DATE_LANGUAGE = SPANISH') day_name  
from   dual
```
```
DAY_NAME
Lunes
```

DY
Monday

DAY_NAME
Lunes

Statement 9

when you exclude FM from the format mask, the database right pads the day name. This is up to the length of the longest name in the given language.

select to_char(date'2018-01-01', 'Day', 'NLS_DATE_LANGUAGE = CROATIAN') day_name,  
       length(to_char(date'2018-01-01', 'Day', 'NLS_DATE_LANGUAGE = CROATIAN')) monday_length,  
       length(to_char(date'2018-01-02', 'Day', 'NLS_DATE_LANGUAGE = CROATIAN')) padded_tuesday_length,  
       length(to_char(date'2018-01-02', 'FMDay', 'NLS_DATE_LANGUAGE = CROATIAN')) unpadded_tues_length 
from   dual


DAY_NAME MONDAY_LENGTH PADDED_TUESDAY_LENGTH UNPADDED_TUES_LENGTH
Ponedjeljak 11 11 6

DAY_NAME	MONDAY_LENGTH	PADDED_TUESDAY_LENGTH	UNPADDED_TUES_LENGTH
Ponedjeljak	11	11	6

Statement 10

alter session set nls_territory = AMERICA

Statement processed.

Statement 11
The day-of-week number is in the range 1-7. But the value depends on the NLS_territory! The USA considers Sunday to be the start of week, so Monday is day 2.
```
select to_char(date'2018-01-01', 'd') day_number  
from   dual
```
```
DAY_NUMBER
2
```

DAY_NUMBER
2

Statement 12

alter session set nls_territory = "UNITED KINGDOM"

Statement processed.

Statement 13
But many other countries consider Monday the start of the week. So in the UK, Monday is day 1. You can't pass the NLS_territory to to_char. So be careful when using this conversion.
```
select to_char(date'2018-01-01', 'd') day_number  
from   dual
```
```
DAY_NUMBER
1
```

DAY_NUMBER
1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database