-3

I'm trying to create a function that returns the day of the week for a specified date, and I tried the one that's on the bottom, but unsuccessful.

SELECT DAYOFWEEK(‘2015-07-04’);
SGIL
  • 115
  • 2
  • 2
  • 9
  • possible duplicate of [How to get the week day name from a date?](http://stackoverflow.com/questions/8004645/how-to-get-the-week-day-name-from-a-date) – Alex Poole Jul 03 '15 at 08:06
  • Different DBMS use different flavours of SQL. Date and Time is usually something where they differ a lot. So when you google for a functionality, specify the DBMS, Oracle in your case. Your SQL is invalid in Oracle, but is probably valid in another DBMS. – Thorsten Kettner Jul 03 '15 at 08:22
  • Thank you for your feedback, however, I got that syntax from an Oracle website, and I just pasted and copied, and it still didn't work. – SGIL Jul 03 '15 at 19:17
  • Then it must be MySQL. Oracle bought SUN who had MySQL, so they now have their own Oracle DBMS *and* MySQL. That means one has to be more attentive which DBMS they are talking about on their Websites. – Thorsten Kettner Jul 03 '15 at 19:41

2 Answers2

1

Use TO_CHAR for that.

select 
  to_char(date'2015-07-04', 'Day'), 
  to_char(date'2015-07-04', 'Day', 'NLS_DATE_LANGUAGE=american'), 
  to_char(date'2015-07-04', 'Day', 'NLS_DATE_LANGUAGE=german'), 
  to_char(date'2015-07-04', 'DY'),
  to_char(date'2015-07-04', 'D') -- result depends on NLS_TERRITORY which you cannot specify here unfortunately
from dual;

The string delimiter in SQL is ', but I guess that was just a copy & paste error? A date literal in Oracle starts with DATE. To select a single value in Oracle, select from DUAL. You can optionally specify a language when you want to see weekday names. As to the day number, this depends on a session setting, unfortunately, with 1 being either Sunday or Monday.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 1
    Sunday and Monday are the most common start days of a week. However, there are territories where the weeks starts on Saturday (most Arabic countries) and even Friday (for Bangladesh) – Wernfried Domscheit Jul 03 '15 at 09:50
  • 1
    @Wernfried: Wow, even that. I didn't know this. I never use 'D' for being session dependent, but always use 'DY' with the language set to 'English' in order to know the weekday, as this is guaranteed to work in every installation, independent from any settings. – Thorsten Kettner Jul 03 '15 at 09:58
  • @ThorstenKettner, thank you very much. What I missed was FROM command and different format. – SGIL Jul 03 '15 at 19:23
0

Try: select to_char(to_date('2015-07-04','yyyy-dd-mm'), 'DAY') I think it should work.

Referred from: How to get the week day name from a date?

Community
  • 1
  • 1
abhisekG
  • 426
  • 2
  • 5
  • 16
  • You should not depend on your locale-specific NLS settings. It might not work the same for someone whose NLS_DATE_LANGUAGE is different. – Lalit Kumar B Jul 03 '15 at 08:35
  • Yes I agree. Thank you for pointing that out. But the question is specifically '2015-07-04'. How do you suppose answering it? – abhisekG Jul 03 '15 at 09:12
  • @abhisekG, thank you for the reference, but it's missing a FROM line, and it will not work without it. Thank you – SGIL Jul 03 '15 at 19:23
  • yes.. actually I had been working on NETEZZA which didn't not need "FROM dual". =) – abhisekG Mar 15 '16 at 05:14