PLSQL Replace Hours in a date

Question

I want to replace the hour on a date, for instance, if I have 07/07/13 11:15, I'll want to get 07/07/13 00:00 from the first date.

So, what I'm trying to do is this:

textDate := TO_CHAR (auxDate, 'DD/MM/RR')||' '||textHour||':'||textMin;
endDate := TO_DATE(textDate, 'DD/MM/RR hh:mm');

When I run the SP, it gives me the following error:

ORA-01810: format code appears twice

auxDate is declared as DATE, textDate as VARCHAR2(20), endDate is DATE.

Can you help me solve this?

score 1 · Answer 1 · answered Jul 10 '13 at 12:47

1

you have a typo in to_date format. it must be mi and not mm

endDate := TO_DATE(textDate, 'DD/MM/RR hh:mi');

answered Jul 10 '13 at 12:47

schurik

7,798
2
23
29

score 0 · Accepted Answer · answered Jul 10 '13 at 12:25

0

Reset your date to midnight and then add required amount of hours and minutes.

endDate := trunc(auxDate) + textHour/24 + textMin/24/60;

answered Jul 10 '13 at 12:25

Kirill Leontev

10,641
7
43
49

the_slk · Answer 3 · 2013-07-10T13:53:31.110

DECLARE
    textDate_YYYYMMDD VARCHAR2(8)  := '20130101';
    textDate_HH       VARCHAR2(2)  := '13';
    textDate_MM       VARCHAR2(2)  := '15';
    textDate_mask     VARCHAR2(14) := 'YYYYMMDDHH24MI';
    textDate          DATE         := TO_DATE(textDate_YYYYMMDD || textDate_HH || textDate_MM, textDate_mask);
BEGIN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(textDate, textDate_mask));
END;
/*
anonymous block completed
201301011315
*/

PLSQL Replace Hours in a date

3 Answers3