0

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.

APC
  • 144,005
  • 19
  • 170
  • 281
SASUSMC
  • 681
  • 4
  • 20
  • 38
  • 1
    https://stackoverflow.com/questions/8004645/how-to-get-the-week-day-name-from-a-date –  Aug 14 '19 at 17:09
  • It's a good idea to check the [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/index.html) rather than just being puzzled when something you typed turns out not to be an actual thing. – William Robertson Aug 14 '19 at 22:10

1 Answers1

5

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')
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • How come `WeekDay` [shows up in the function reference](https://docs.oracle.com/cd/E41183_01/DR/WeekDay.html)? – citynorman Sep 10 '20 at 02:46
  • 1
    @citynorman - that documentation is for Oracle DAL (data access layer), not Oracle RDBMS. They have a lot of products, including other DBs like MySQL, all documented on the same site - which can make search results confusing. Particularly when the product name isn't in the URL, or as in this case, anywhere on the page. – Alex Poole Sep 10 '20 at 05:59