Comparison between day of week in oracle

0
votes

I have problems with comparing the day of week

Here is my query in oracle database.

  SELECT DAY, WEEKDAY
    FROM (SELECT v.TNI, v.FRMP, v.LR, v.DAY, v.HH, v.VOLUME,
    CASE WHEN hd.HOLIDAY_DATE is not null then 'HOLIDAY'
    ELSE to_char(v.DAY, 'Day') END AS WEEKDAY
    FROM v_nem_rm16 v
    LEFT JOIN DBP_ADMIN.DBP_HOLIDAY hd 
    ON v.DAY = hd.HOLIDAY_DATE
    WHERE v.STATEMENT_TYPE != 'FORECAST')
  WHERE WEEKDAY = 'Monday';

Basically, the "WEEKDAY" column has the day of week based on the "DAY". Day is just date like 13/Mar/17

If you look at the "CASE" statement, you will notice that the "WEEKDAY" column is filled by "to_char(v.DAY, 'Day')".
So the column has values like "Sunday, Saturday and so on, the day of week".

The problem is the outer query's where clause, "WHERE WEEKDAY = 'Monday'"
When I execute this query, It does not give me any rows even if I have rows having Monday as the value in "WEEKDAY" column



But when I change the WHERE clause to "WHERE WEEKDAY = to_char(sysdate, 'Day')", It works fine. The sql statement gives me the rows having "Saturday" in "WEEKDAY" column since the "to_char(sysdate, 'Day')" gives me "Saturday".

So what is problems with my first query??

I just want to filter rows by the name of the day of week like if i pass "Monday", i want to have all the rows having " Monday" in "WEEKDAY" column.

How can I do??

THANKS GUYS

2
oracledate
Try to_char(v.DAY, 'fmDay'). Be aware, result depends on current session NLS_DATE_LANGUAGE value which may change at any time. – Wernfried Domscheit

2 Answers

1
votes

It appears TO_CHAR(, 'Day') returns a fixed length string with right side padding of blanks. So, you are not getting 'Monday', you are really getting 'Monday '. The longest day is 'Wednesday', so it is a 9 character string. So, either trim the value or compare against 'Monday ' exactly.

select '|'||to_char(sysdate+level,'Day') ||'|' days_of_the_week
from dual
connect by level <= 7;

Results...

|Saturday |
|Sunday   |
|Monday   |
|Tuesday  |
|Wednesday|
|Thursday |
|Friday   |
0
votes

if you would like to use the day names (either full or abbreviated), I suggest to specify the language in the to_char function as follows:

select '|'||to_char(sysdate+level, 'fmDay', 'NLS_DATE_LANGUAGE = American') ||'|' days_of_the_week_enu,
       '|'||to_char(sysdate+level, 'fmDay', 'NLS_DATE_LANGUAGE = German') ||'|' days_of_the_week_ger,
       '|'||to_char(sysdate+level, 'DY', 'NLS_DATE_LANGUAGE = American') ||'|' days_of_the_week_abbr_enu,
       '|'||to_char(sysdate+level, 'DY', 'NLS_DATE_LANGUAGE = German') ||'|' days_of_the_week_abbr_ger
from dual
connect by level <= 7;

This way the returned day names will be independent of session locale settings.

Result: enter image description here