Why I can't perform this simple insert operation? How can I solve this date format issue?

Question

I am not so into database and I have the following problem trying to implement a simple insert query that involve a date field on an Oracle database.

So I have a table named FLUSSO_XMLSDI that have the following structure (this is the result of a select *):

NUMERO_FATTURA    DATA_EMISSIONE    XML
-----------------------------------------------------------
2502064160        11-GEN-2014       some text
2502064161        15-GEN-2014       some text
2502064162        25-GEN-2014       some text

Where the DATA_EMISSIONE field is a DATE type.

Now, I have to insert a new record using values extracted by an XML and I do something like this:

INSERT INTO FLUSSO_XMLSDI (NUMERO_FATTURA, DATA_EMISSIONE, XML)
VALUES (2503985924, 2015-06-16, 'TEST');

But I have some problem with the DATA_EMISSIONE field because I obtain the following error message trying to performing the previous query:

Errore con inizio alla riga 3 nel comando:
INSERT INTO FLUSSO_XMLSDI (NUMERO_FATTURA, DATA_EMISSIONE, XML) 
VALUES (2503985924, 2015-06-16, 'TEST') 
Errore alla riga del comando:4 Colonna:27
Report errori:
Errore SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

As you can see in the data obtained from the select operation the DATA_EMISSIONE field contains date in this format: 11-GEN-2014 but I am trying to insert it as 2015-06-16 (because it is how my application obtain this value from the XML)

Is this the problem? There is not some sort of automatic conversion between different data format automatically performed by Oracle? How can I solve this issue and correctly perform my insert statment?

Answer 1

2015-06-16 is a number, indeed. Specifically, it is 1993 (2015 minus 6 minus 16).

What you need to do is ...

INSERT INTO FLUSSO_XMLSDI (NUMERO_FATTURA, DATA_EMISSIONE, XML)
VALUES (2503985924, date'2015-06-16', 'TEST');

... if you want to use a date literal constant, or (more probable, since you get this data from an XML elsewhere) ...

INSERT INTO FLUSSO_XMLSDI (NUMERO_FATTURA, DATA_EMISSIONE, XML)
VALUES (2503985924, to_date('2015-06-16', 'yyyy-mm-dd'), 'TEST');

... if you want to use a character string value, possibly from a bind variable.

Answer 2

You can use the TO_DATE Oracle function to perform data conversions, like this

INSERT INTO FLUSSO_XMLSDI (NUMERO_FATTURA, DATA_EMISSIONE, XML)
VALUES (2503985924, TO_DATE('2015-06-16', 'yyyy-mm-dd'), 'TEST');

You can find more information on this function here