Counting date in oracle

Question

I write query to check if the last day is Friday but always I see 2 not 4. Any advice what is wrong with that query ?

 (
 SELECT (
        CASE
               WHEN To_char(due_date,'DAY','nls_date_language=english')='FRIDAY' THEN 4
               ELSE 2
        END)
 FROM   capagency.pa_instances pa,
        capagency.users u
 WHERE  pa.idusr = u.idusr
 AND    pa.entity_id = a.ceid
 AND    p.payment_date BETWEEN pa.begin_date AND    pa.due_date.

Ok, i added trim but that still results that i obtain are wrong, instead of value 4 i got value 2.

(
select convert(u.Full_Name, 'UTF8')
from Capagency.Pa_Instances Pa,
     Capagency.Users u
where Pa.Idusr = u.Idusr
AND Pa.Entity_Id = a.Ceid
and p.payment_date BETWEEN pa.begin_Date and Pa.DUE_DATE + (
    CASE WHEN (TRIM(TO_CHAR(due_date,'DAY','nls_date_language=english'))='FRIDAY')
      THEN 4 ELSE 2 END)
and rownum = 1
) "Collector",

First I'm getting

(select case
  when (TRIM(To_char(due_date,'DAY','nls_date_language=english'))) = 'FRIDAY'
    THEN 4 else 2 END
from Capagency.Pa_Instances Pa,
     Capagency.Users u
where Pa.Idusr = u.Idusr
AND Pa.Entity_Id = a.Ceid
and p.payment_date BETWEEN pa.begin_Date and Pa.DUE_DATE

So I see that day is Friday but still getting value 2 from the else. I know how between works, and for some days that aren't Friday that works OK, I obtain the correct values.

I'm wondering still why if I check

case when (TRIM(To_char(due_date,'DAY','nls_date_language=english'))) = 'FRIDAY'

it is always false so my value is always 2 not 4.

Answer 1

This should hopefully demonstrate what's going on:

select to_char(to_date('13/02/2015', 'dd/mm/yyyy'), 'DAY','nls_date_language=english') dy,
       replace(to_char(to_date('13/02/2015', 'dd/mm/yyyy'), 'DAY','nls_date_language=english'), ' ', '*') dy_show_spaces,
       to_char(to_date('13/02/2015', 'dd/mm/yyyy'), 'fmDAY','nls_date_language=english') fmdy,
       replace(to_char(to_date('13/02/2015', 'dd/mm/yyyy'), 'fmDAY','nls_date_language=english'), ' ', '*') fmdy_show_spaces
from   dual;

DY        DY_SHOW_SPACES FMDY   FMDY_SHOW_SPACES
--------- -------------- ------ ----------------
FRIDAY    FRIDAY***      FRIDAY FRIDAY      

See also.

In short, the fm modifier negates the need to use TRIM on the resultant string.

Answer 2

From the docs,

DAY format model -

Name of day, padded with blanks to display width of the widest name of day in the date language used for this element.

You need to TRIM the spaces right padded to the DAY format. Modify your query to -

TRIM(To_char(due_date,'DAY','nls_date_language=english'))='FRIDAY'

For example,

SQL> SELECT LENGTH(trim(TO_CHAR(hiredate, 'DAY', 'nls_date_language=AMERICAN'))) dt_trim,
  2    LENGTH(TO_CHAR(hiredate, 'DAY', 'nls_date_language=AMERICAN')) dt,
  3    TO_CHAR(hiredate, 'DAY', 'nls_date_language=AMERICAN') DAY
  4  FROM emp
  5  /

   DT_TRIM         DT DAY
---------- ---------- ---------
         9          9 WEDNESDAY
         6          9 FRIDAY
         6          9 SUNDAY
         8          9 THURSDAY
         6          9 MONDAY
         6          9 FRIDAY
         7          9 TUESDAY
         8          9 THURSDAY
         7          9 TUESDAY
         7          9 TUESDAY
         9          9 WEDNESDAY

   DT_TRIM         DT DAY
---------- ---------- ---------
         8          9 THURSDAY
         8          9 THURSDAY
         8          9 SATURDAY

14 rows selected.

SQL>

So you see the spaces right padded to the DAY column? I would say better use DY format, which has only 3 characters.

Or, use TRIM. Let's look at a similar test case using HIREDATE column in EMP table -

SQL> SELECT
  2    CASE
  3      WHEN trim(TO_CHAR(hiredate, 'DAY', 'nls_date_language=AMERICAN'))='FRIDAY'
  4      THEN 4
  5      ELSE 2
  6    END dt,
  7    TO_CHAR(hiredate, 'DAY', 'nls_date_language=AMERICAN') dt_1
  8  FROM emp
  9  /

        DT DT_1
---------- ---------
         2 WEDNESDAY
         4 FRIDAY
         2 SUNDAY
         2 THURSDAY
         2 MONDAY
         4 FRIDAY
         2 TUESDAY
         2 THURSDAY
         2 TUESDAY
         2 TUESDAY
         2 WEDNESDAY

        DT DT_1
---------- ---------
         2 THURSDAY
         2 THURSDAY
         2 SATURDAY

14 rows selected.

SQL>