I have a function that takes in a string 'HH:MM:SS' and converts it to the number of seconds. See below example
CREATE OR REPLACE FUNCTION CONVERT_TO_SECONDS(
i_date_string IN VARCHAR2
)
RETURN INTEGER
AS
l_hours NUMBER;
l_minutes NUMBER;
l_seconds NUMBER;
BEGIN
SELECT trim('"'
FROM regexp_substr(i_date_string,'".*?"|[^:]+',1,1)) hours,
trim('"'
FROM regexp_substr(i_date_string,'".*?"|[^:]+',1,2)) minutes,
trim('"'
FROM regexp_substr(i_date_string,'".*?"|[^:]+',1,3)) seconds
INTO l_hours ,
l_minutes ,
l_seconds
FROM dual ;
return
l_hours*3600 +
l_minutes*60 +
l_seconds;
END;
/
SELECT CONVERT_TO_SECONDS('08:08:08') FROM DUAL;
CONVERT_TO_SECONDS('08:08:08')
29288
I have a procedure that works fine, which creates emp_attendance rows.
After the rows are created I am trying to update the end_date of each row with the number of seconds returned by the function. Is this possible? If so, how can I get past the syntax error on the update.
Thanks in advance to all that answer and for your help, patience and expertise.
My test CASE is below.
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE OR REPLACE TYPE nt_date IS TABLE OF DATE;
/
CREATE OR REPLACE FUNCTION generate_dates_pipelined(
p_from IN DATE,
p_to IN DATE
)
RETURN nt_date PIPELINED DETERMINISTIC
IS
v_start DATE := TRUNC(LEAST(p_from, p_to));
v_end DATE := TRUNC(GREATEST(p_from, p_to));
BEGIN
LOOP
PIPE ROW (v_start);
EXIT WHEN v_start >= v_end;
v_start := v_start + INTERVAL '1' DAY;
END LOOP;
RETURN;
END generate_dates_pipelined;
/
Create table employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
card_num VARCHAR2(10),
work_days VARCHAR2(7)
);
INSERT INTO employees (
employee_id,
first_name,
last_name,
card_num,
work_days
)
WITH names AS (
SELECT 1, 'John', 'Doe', 'D564311','YYYYYNN' FROM dual UNION ALL
SELECT 2, 'Justin', 'Case', 'C224311','YYYYYNN' FROM dual UNION ALL
SELECT 3, 'Mike', 'Jones', 'J288811','YYYYYNN' FROM dual UNION ALL
SELECT 4, 'Jane', 'Smith', 'S564661','YYYYYNN' FROM dual
) SELECT * FROM names;
CREATE TABLE emp_attendance(
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
start_date DATE,
end_date DATE,
week_number NUMBER(2));
CREATE OR REPLACE PROCEDURE create_emp_attendance (
p_start_date IN DATE,
p_end_date IN DATE
)
IS
BEGIN
INSERT INTO emp_attendance ( employee_id, start_date, end_date, week_number)
SELECT
employee_id
, start_date
, start_date+NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(3600,43200)), 'SECOND') AS end_date
, to_char(start_date,'WW') AS week_number
FROM ( -- Need subquery to generate end_date based on start_date.
SELECT e.employee_id, d.COLUMN_VALUE + NUMTODSINTERVAL(FLOOR(DBMS_RANDOM.VALUE(0,86399)), 'SECOND') AS start_date
FROM employees e
CROSS JOIN TABLE( generate_dates_pipelined(p_start_date, p_end_date) ) d
) ed
;
END;
/
EXEC create_emp_attendance(SYSDATE, SYSDATE);
-- Having problem with this update
update emp_attendance
set end_date=start_date + NUMTODSINTERVAL
CONVERT_TO_SECONDS('08:08:08'),'SECOND';
-- Once the update is working the query below should show 8hrs 8mins 8sec for each row.
select e.employee_id,
e.first_name,
e.last_name,
trunc(sum(a.end_date - a.start_date) * 24) hours,
trunc(mod(sum(a.end_date - a.start_date) * 24 * 60,60)) minutes,
round(mod(sum(a.end_date - a.start_date) * 24 * 60 * 60,60)) seconds
from employees e,
emp_attendance a
where a.employee_id = e.employee_id
AND start_date BETWEEN TRUNC(SYSDATE)
AND
TRUNC(SYSDATE)+ (1-1/24/60/60)
group by e.employee_id, e.first_name, e.last_name
order by e.employee_id, e.first_name,
e.last_name;
You can simplify your function to:
CREATE OR REPLACE FUNCTION CONVERT_TO_SECONDS(
i_date_string IN VARCHAR2
)
RETURN INTEGER DETERMINISTIC
AS
BEGIN
RETURN ( TO_DATE(i_date_string, 'HH24:MI:SS')
- TO_DATE('00:00:00', 'HH24:MI:SS')
) * 86400;
END;
/
Then you need brackets around the NUMTODSINTERVAL function arguments:
UPDATE emp_attendance
SET end_date = start_date + NUMTODSINTERVAL( CONVERT_TO_SECONDS('08:08:08'),'SECOND' );
db<>fiddle here