I can clearly See combinations of Friday and Monday in this query:
SELECT DISTINCT TO_CHAR(DATE_CASE_CLOSED, 'Day') AS DAY_CLOSED, TO_CHAR(DATE_REPORT_SUBMITTED, 'Day') AS DAY_SUBMITTED
from V_MY_DATA
But when I then do this, I get no records:
select * from V_MY_DATA
WHERE TO_CHAR(DATE_CASE_CLOSED, 'Day') = 'Friday'
Update: Seems the result is a char (padded).....
CodePudding user response：
Seems the result is a char (padded)
It is padded.
Many datetime format elements are padded with blanks or leading zeroes to a specific length.
and:
The character elements MONTH, MON, DAY, and DY are padded with trailing blanks to the width of the longest full month name, the longest abbreviated month name, the longest full date name, or the longest abbreviated day name, respectively, among valid names determined by the values of NLS_DATE_LANGUAGE and NLS_CALENDAR parameters. For example, when NLS_DATE_LANGUAGE is AMERICAN and NLS_CALENDAR is GREGORIAN (the default), the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to nine display characters.
You can use the FM format modifier to avoid the padding; so do:
TO_CHAR(DATE_CASE_CLOSED, 'FMDay')
and to be safe specify the language:
TO_CHAR(DATE_CASE_CLOSED, 'FMDay', 'NLS_DATE_LANGUAGE=English')
CodePudding user response：
See the section on Datetime Format Elements
Also FM