Date to oracle conversion

Question

I’m having a problem, I’m trying to save a date on oracle with formatted dd/MM/yyyy, however, in the bank it presents the date in the format dd/MM/yy.

So, I got the question, if the type I used in the table is not suitable, or, if the date I pass to the bank that is wrong.

well, in my DAO (in java), to include the date, I convert it with the following methods:

    public static java.sql.Date converteParaBanco(Date data) {
            return new java.sql.Date(data.getTime());
    }

   public static Date converteDoBanco(java.sql.Date data) {
            return new Date(data.getTime());
   }

applying the method in the DAO:

ps.setDate(5, ConverteData.converteParaBanco(pojoCompra.getDataCompra()));

giving a system.out on the line above, I realized, that my method makes the date stay in the format of 2017-09-22.

My attribute in the bank, it’s like DATE.

What I could do to have the date saved and also returned in the format of dd/MM/yyyy ?

Answer 1

1º only in the session the user change the format

sql> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';
sql> select sysdate from dual;
SYSDATE

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY HH24:MI:SS';
--Sessão alterada.
SQL> select sysdate from dual;
SYSDATE

NLS_DATE_FORMAT = ‘DD/MM/YY HH24:MI:SS’ this is the oracle’s date string format.

You can change the pattern if you are using SQLDeveloper

According to the following steps:

• Tools > Preferences.
• In the window that opens Select Database > NLS
• In Date Format put the desired format