Search code examples
sqloracle-databasetrim

SQL: How to properly nest TRIM() method

I'm working on a homework problem, and my function works fine. I accomplished what my professor is asking, but I need help formatting my last statement using the Trim() method. I don't know how to properly nest it with 2 other methods. I did it properly on the first line, but I'm having difficulty with line 3 to format the dates for the "FIRST_PAY DAY" column. Below is my working function with one Trim() method-

SQL> SELECT empno, ename, sal, TRIM(TO_CHAR(hiredate, 'Month')) || ' ' || 
     TO_CHAR(hiredate, 'fmddTH, YYYY') AS "HIRE DAY",
  2  TO_CHAR(NEXT_DAY(hiredate, 'FRIDAY'),
  3  'Month fmddTH, YYYY') "FIRST_PAY DAY"
  4  FROM emp;

Thank you!

Here is the code I tried doing, but to no luck-

SQL> SELECT empno, ename, sal, TRIM(TO_CHAR(hiredate, 'Month')) || ' ' || 
     TO_CHAR(hiredate, 'fmddTH, YYYY') AS "HIRE DAY",
  2  TO_CHAR(NEXT_DAY(hiredate, 'FRIDAY'),
  3  TRIM(TO_CHAR('Month')) || ' ' || TO_CHAR('fmddTH, YYYY')) 
     "FIRST_PAY DAY"
  4  FROM emp;

"HIRE DAY" column is formatted correctly with TRIM() method, but "First_PAY DAY" column isn't. Below is a portion of my output from both date columns-

  HIRE DAY              FIRST_PAY DAY
 ___________________   ___________________
 May 1st, 1981         May       8th, 1981
Solution 
  • select to_char(hiredate,'fmMonth ddth, yyyy') as hiredate,
to_char(next_day(hiredate, 'FRIDAY'),'fmMonth ddth, yyyy') as payday
from emp;

    Anything wrong with the above?

    Best regards, Stew Ashton