How to format DD / MM / YYYY in a Date type field of a table in oracle?

Question

How to format DD / MM / YYYY in a Date type field of a table in oracle?

Navigation

#1 by (1 votes)

0

I have a query which gets some Date type fields

The result of this query is downloaded to a .csv file and what it does is that it does NOT show the date fields in DD / MM / YYYY format, try to convert them with to_char but when you show them in the excel they all come out with the same date and another year that nothing to see and when trying with to_date the dates are correct but in another format, something like this Sat Jun 14 00:00:00 18

SELECT F.ID, F.NOMBRE, TO_DATE(F.FECHA_INI,'DD/MM/YYYY') FECHA_INI, TO_DATE(F.FECHA_REGISTRO,'DD/MM/YYYY') FECHA_REGISTRO, TO_DATE(F.FECHA.CARGA,'DD/MM/YYYY') FECHA CARGA, TO_CHAR(F.IMPORTE_UNO,'fm9990.00'), TO_CHAR(F.IMPORTE_DOS,'fm9990.00')
FROM FACTURA F
WHERE (?1 IS NULL OR  F.ID =?1)
AND F.FECHA_INI &gt;= TO_DATE(?2 'DD/MM/YYYY')
AND F.FECHA_INI &lt;= TO_DATE(?3 'DD/MM/YYYY')

Why does that format come out and not the one I put in DD / MM / YYYY?

What makes me fata or is it wrong? It will be a question of how excel interprets the data?

I have the following code that I call from my controller to generate the .csv file

if(factList !=null) {
    response.addHeader("Content-Type", "application/csv");
    response.addHeader("Content-Disposition", "attachment;filename=archivo.csv");
    PrintWriter out = response.getWriter();
    out.write("ID,NOMBRE,FECHA_INI, FECHA_REGISTRO");
    out.write("\n");
    for(String factu: factList){
        out.write(factu.toCSVRepresentation()); 
        out.write("\n"); 
    } 
    out.flush(); 
    out.close(); 
}else{ 
    log.info("Esta vacio"); 
} 
}catch(ControlException e){ throw new ControlException("Error", e); 
} 
}

sql java oracle date

asked by Root93 25.07.2018 в 05:46

source

1 answer

How to iterate this javascript object in C # (or the correct way to send with AJAX) in ASP.NET Dictionary, access the password. c # .net

score 1 · Accepted Answer

As the comrades say, and assuming the date fields are of type DATE , it should work with TO_CHAR(campo_fecha, 'DD/MM/YYYY') .

If we continue assuming that these fields are of DATE , TO_DATE(F.FECHA_INI,'DD/MM/YYYY') does not work since TO_DATE() only converts fields of type CHAR , VARCHAR2 , NCHAR , or NVARCHAR2 .

The only way I know of using TO_DATE() with a date, would be to use only the first parameter of the function, resulting in the date as it is in the database: for example, if you have the field FECHA in BBDD with value 25/07/2018 10:28:00 , then TO_DATE(FECHA) would result in 25/07/2018 10:28:00 .

An easy way to convert those date fields to the format you want, would be by formatting them in your java code.

For this you create a variable with the format you want of type SimpleDateFormat , and then apply the format to the date:

private static SimpleDateFormat f = new SimpleDateFormat("dd/MM/yyyy");
...

String fecha = f.format(FECHA_INI);

In your code, you can apply it to each of the date fields when you create the String of the rows to export to the .csv , and in the query to take the dates without formatting:

SELECT F.ID, F.NOMBRE, F.FECHA_INI, F.FECHA_REGISTRO, F.FECHA.CARGA, TO_CHAR(F.IMPORTE_UNO,'fm9990.00'), TO_CHAR(F.IMPORTE_DOS,'fm9990.00')
FROM FACTURA F
....