CODEWITHSUNDEEP
oracle10g
mlwacosmos
mlwacosmos

Reputation: 4541

Dates with Oracle

Iam using Oracle 10g express.

I write this SQL order :

INSERT INTO compte_courant (num_compte_courant, num_client, libelle_compte_courant, solde_compte_courant, decouvert_compte_courant, taux_agios_compte, date_ouverture_compte_courant, date_fermeture_compte_courant, etat_compte_courant) VALUES
('0001A1234', S_CLIENT.CURRVAL, 'compte courant', 1000.00, -300, 5.2, TO_DATE('01/01/2011', 'DD/MM/YYYY'), NULL, 'open');

When I look in the compte_courant table. this is what I see in the date field : 01/01/11

in a stored procedure I write : 

select TO_DATE(date_operation_transfert, 'DD/MM/YYYY'), TO_DATE(date_valeur_transfert, 'DD/MM/YYYY'), montant_transfert, compte_cible
  from transfert
  where compte_cible = numAccount
  order by date_operation_transfert desc;

On the java side, when I read my ResultSet, I have : Janvier 01, 0011

How could that be ? All I want is Oracle to store the date as I asked to : DD/MM/YYYY

Thanks for your help

Upvotes: 2

Views: 549

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

If you really want Oracle to store the date "as you asked it to", what you are dealing with is a string, not a date. However, if it's a date, I recommend you store it as a date.

Oracle stores dates using a single, internal, data format - when you query a date it formats it for display according to the supplied date format. Your java client is simply using a different date format.

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

You should not apply the TO_DATE function in your SELECT since the values are already dates. To convert them to strings use TO_CHAR.

When you select TO_DATE(datevalue), Oracle finds that TO_DATE requires a string argument, so it does an implicit TO_CHAR(datevalue) to make it work. The implicit TO_CHAR uses the default format mask, which often omits the century.

Upvotes: 3

Related Questions