CODEWITHSUNDEEP
sqloracle-databasedate-formatting
SASUSMC
SASUSMC

Reputation: 681

How to identify Weekdays in Oracle?

Good Morning,

I am trying to find an alternative to Weekday for Oracle, for some reason our version does not recognize the Weekday function. It keeps throwing an error,

WeekDay: Invalid Identifier.

I am trying to use it in a Where Statement:

Where
Weekday(OR_Log.Surgery_DAte) not in (1,7)

To weed out any Saturday or Sunday surgeries. This is to update code from Teradata to Oracle.

Oracle V. 12C

Any help would be appreciated.

Upvotes: 0

Views: 1396

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

Your version of Oracle doesn't recognise 'weekday' because that is not an Oracle function.

You can use the to_char() function to get a day number, but it's dependent on NLS settings, so safer not to reply on it. Day names are also NLS-language-dependent, but that can at least be overridden as part of the function call:

where to_char(OR_Log.Surgery_Date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') not in ('Sat', 'Sun')

The 'Dy' format element is described in the documentation, along with all the others. Note that 'DY' gives you the day abbreviation in uppercase, and 'Day' or 'DAY' give you the full day name in mixed/uppercase; but those are padded with spaces by default (as are abbreviations in other languages...); but you could add a modifier if you want the full day names for readability:

where to_char(OR_Log.Surgery_Date, 'FMDay', 'NLS_DATE_LANGUAGE=ENGLISH') not in ('Saturday', 'Sunday')

Upvotes: 5

Related Questions