I have the following insert
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 inserts an empty value.
But if it has a valid value for date, it inserts the date / time value.
The problem:
With NVL, it is only saving the date , even with the valid value of date / time , I want to save data/hora
Solution: put NVL within 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 NVL function will have a return of the same type from the first parameter, unless it is null (in this case it considers the type of the second parameter). So when an empty string is sent as the first parameter the NVL function will have a return of type varchar .
For example, run the query below that shows the type of each query column:
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 return of the NVL function when with the first parameter null is of type date (Typ = 13) in which case the insert would work. However, the return of this same function passing the empty string (as in its example) is of type varchar (Typ = 1). *
So, if the first parameter is the empty string, Oracle needs to convert the other parameters to varchar because it must have a return of the same type. For this conversion it uses the date format that is defined in the session at the time the insert is executed. Because this format generally does not include the time, the date is converted to the text format without the time. To verify this execute the command below before your insert and the date will be recorded in the correct format:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'
* Further explanation of the dump function here . Table with the codes for each type here .