How to retrieve day of a week from date in SQL?-CodePudding

Column in a record, which is declared as vPracownik.zatrudniony is in a format like this 12/31/1967, 11:00:00 PM. I want it to be a parameter of DATEPART(dw, vPracownik.zatrudniony) . It throws an error: ORA-06550: line 11, column 13: PLS-00201.

All code looks like this:

DECLARE
    CURSOR cPracownicy IS
        SELECT *
        FROM Pracownicy 
        FOR UPDATE;
    vPracownik Pracownicy%ROWTYPE;

BEGIN
    FOR vPracownik IN cPracownicy LOOP
        
        IF (DATEPART(dw, vPracownik.zatrudniony) = 2) THEN
            UPDATE Pracownicy
            SET placa_pod = 1.2 * placa_pod
            WHERE CURRENT OF cPracownicy;

            DBMS_OUTPUT.PUT_LINE(
            vPracownik.nazwisko ||
            vPracownik.placa_pod
            );
        END IF;
        
    END LOOP;
END;

I have also tried using ((SELECT TO_CHAR(DATE(vPracownik.zatrudniony), 'MM/dd/yyyy', 'DAY') FROM DUAL) = 'MONDAY') in an IF statement. How to correctly retrieve day from date?

CodePudding user response：

First convert string to a valid date value, then apply to_char to it - with desired format mask - to retrieve day name.

Something like this:

SQL> with test (col) as
  2    (select '12/31/1967, 11:00:00 PM' from dual)
  3  select
  4          to_date(col, 'mm/dd/yyyy, hh:mi:ss am') val_date,
  5  to_char(to_date(col, 'mm/dd/yyyy, hh:mi:ss am'), 'Day', 'nls_date_language = english') val_day
  6  from test;

VAL_DATE            VAL_DAY
------------------- ---------
31.12.1967 23:00:00 Sunday

SQL>

What is DATEPART supposed to be? As far as I can tell, there's no such built-in function ...

CodePudding user response：

From Google translate, "Pracownik zatrudniony" is something like "employee hire date" - is that true?

Checking in Linux:

$ d="12/31/1967"
$ date -d $(echo $d|awk -F/ '{print $3 "/" $1 "/" $2}')  %A # DOW
Sunday

Checking in Oracle:

SYS@ORCL19C> select to_char( to_date( '12/31/1967, 11:00:00 PM', 'MM/DD/YYYY, HH12:MI:SS AM'), 'DAY') day_of_hire from dual;

DAY_OF_HIRE
-----------
SUNDAY


SYS@ORCL19C> set serveroutput on
SYS@ORCL19C> BEGIN
  2  IF rTRIM( to_char( to_date( '12/31/1967, 11:00:00 PM', 'MM/DD/YYYY, HH12:MI:SS AM'), 'DAY')) = 'SUNDAY' THEN
  3     dbms_output.put_line('This person was hired on a Sunday!');
  4  ELSE
  5     dbms_output.put_line('Not on a Sunday');
  6  END IF;
  7  END;
  8  /
This person was hired on a Sunday!

PL/SQL procedure successfully completed.