Insert TIME DATE on Oracle

Question

I have the following information

    INSERT INTO tabela 
     (codigo, datahora ) 
     VALUES
     (1, NVL('',TO_DATE('19/07/2017 19:49:00', 'DD/MM/YYYY HH24:MI:SS')))

Why NVL?

Because if it has no valid value for date, it insert an empty value.

But if it has a valid date value, it enter the date/time value.

The problem:

With the NVL, he’s just saving the date, even with the valid value of date/time, I want to save the data/hora

Answer 1

Solution: to place the NVL within the TO_DATE

    INSERT INTO tabela 
    (codigo, datahora ) 
    VALUES
    (1, TO_DATE(nvl('','19/07/2017 19:49:00'),'DD/MM/YYYY HH24:MI:SS'))

---

Justification: The function NVL you will get a return of the same type as the first parameter, unless it is null (in this case consider the type of the second parameter). So when a string empty as first parameter the function NVL will have a return of the type sweep.

For example, run the query below that shows the type of each column of the query:

    select 'Nulo' as tipo, dump(nvl(null,TO_DATE('19/07/2017 19:49:00', 'DD/MM/YYYY HH24:MI:SS'))) from dual union
    select 'Vazio' as tipo, dump(nvl('',TO_DATE('19/07/2017 19:49:00', 'DD/MM/YYYY HH24:MI:SS'))) from dual 

You will see that the function return NVL when with the first paramêtro null is the type date (Typ=13) and so in this case the Insert would work. However, the return of this same function passing the empty string (as in your example) is of type sweep (Typ = 1). *

Thus, in case the first parameter is the empty string, Oracle needs to convert the other parameters to scan because it needs to have a return of the same type. For this conversion it uses the date format that is defined in the session at the time of the Insert execution. As this format usually does not include the time, the date is converted to the text format without the time. To prove this, run the command below before your Insert and the date will be saved in the correct format:

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'

*More explanations about the dump function here. Table with codes for each type here.