SQL & PL/SQL

I'm trying to use the DECODE function to test for NULL before inserting to a DATE field. However, it seems to only insert the DATE, with a "default" time of 12:00 - it isn't properly inserting the time.

Basically I need to test if Date1 is NULL. If it isn't I need to concatenate the DATE from Date1 with the TIME from Date2 to get a full date/time... then insert this new value.

Generic Example:

-----

CREATE TABLE DATETEST (TestID NUMBER(1), TestDate DATE);

-----


DECLARE
v_Date1 DATE;
v_Date2 DATE;


BEGIN

v_Date1 := TO_DATE('01-JAN-11 05:53:12', 'DD-MON-YY HH:MI:SS');
v_Date2 := TO_DATE('08-FEB-11 02:18:31', 'DD-MON-YY HH:MI:SS');

INSERT INTO DATETEST (TestID, TestDate) VALUES ('1', DECODE(v_Date1, NULL, NULL, TO_DATE(To_Char(v_Date1, 'DD-MON-YY') || ' ' || TO_CHAR(v_Date2, 'HH:MI:SS'),'DD-MON-YY HH:MI:SS')));

INSERT INTO DATETEST (TestID, TestDate) VALUES ('2', TO_DATE(To_Char(v_Date1, 'DD-MON-YY') || ' ' || TO_CHAR(v_Date2, 'HH:MI:SS'),'DD-MON-YY HH:MI:SS'));

END;

-----

SELECT TestID, TO_CHAR(TestDate, 'DD-MON-YY HH:MI:SS') from DATETEST;

-----


This example performs two inserts. One with the DECODE function, and one without. The one without inserts the time properly. Can anyone tell me why the one with the DECODE function doesn't? I realize I can use a simple if/then to check if the date is null above and put the date/time in a variable, but since my real scenario is in a large chunk of other stuff, I'm trying to keep it as streamlined as possible.

Edited by: BoredBillJ on Jul 14, 2011 6:39 AM