In conjunction with Apex 4.2
I am working on a PL/SQL process to compare date fields. I have a simple PL/SQL process that compares just the dates. I am comparing a date that is in the database to a date field that is essentially a page item
DECLARE
the_date DATE;
BEGIN
SELECT update_date into the_date
FROM MY_BIG_TABLE
WHERE My_ID = :P32_survey_id;
IF to_date(:P32_UPDATE_DATE) <> to_date(the_date) THEN
:P32_FLAG := 'Y';
ELSE
:P32_FLAG := 'N';
END IF;
END;
The above seems to work fine. However, I need to add more "depth" to the pl/sql process. I need to compare the dates as well as the timestamp on those dates for a more accurate comparison. I have written another pl/sql process but I keep getting an error about the wrong conversion:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
My process is as follows
DECLARE
the_date DATE;
db_date_field varchar2(30);
new_date_field varchar2(30);
BEGIN
SELECT update_date into the_date
FROM MY_BIG_TABLE
WHERE My_ID = :P32_survey_id;
db_date_field := to_char(:P32_UPDATE_DATE, 'DD-MON-YY HH:MI:SS');
new_date_field := to_char(the_date, 'DD-MON-YY HH:MI:SS');
IF db_date_field <> new_date_field THEN
:P32_FLAG := 'Y';
ELSE
:P32_FLAG := 'N';
END IF;
END;
I keep receiving the ORA error listed above. I'm not sure what I'm missing. Any help on this would be greatly appreciated. Thanks in advance