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
oraclesql
Using Oracle SQL, how do you create a result set for:
For example:
DAY NAME 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 Sunday
Best Answer
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:
But the same query run in the UK is a day off:
... and I need to adjust the calculation to correct for that:
You can also specify the language used for the day names separately if you want:
Documentation for
to_char()
withnls_date_language
and day of the week, and more in the globalisation support guide.