CODEWITHSUNDEEP
databasedateplsqlplsqldeveloper
Java_Alert
Java_Alert

Reputation: 1179

PL/SQL Date Insert

I am firing a insert query in 'dd/mm/yyyy' format but it is storing date into MM/DD/YYYY format. I just want to know why it is happening?

This is insert query i am using.

insert into txnblackout(endtime,idtxn,blackoutflag,starttime,startdate,typeuser,id_entity,idsequence,idapp,enddate) values('83520','LGN','D','7920',TO_DATE('30/12/2012','dd/mm/yyyy'),'ECU','B001','4','A1',TO_DATE('30/12/2012','dd/mm/yyyy'))

enter image description here

Upvotes: 0

Views: 2904

Answers (3)

Frank Schmitt
Frank Schmitt

Reputation: 30835

If you don't want to change the Windows date format (as suggested by Colin 't Hart), you can

  • use an explicit to_char() date format in your query (as suggested by Robert Hanson)
  • set your client NLS settings
  • configure your client (since you seem to be using PL/SQL developer): Tools -> Preferences -> NLS options -> Date -> check user defined + enter your format

Personally, I'd set the client NLS settings.

Upvotes: 2

Robert Hanson
Robert Hanson

Reputation: 579

The important bit can be gleamed by your insert, which includes "TO_DATE('30/12/2012','dd/mm/yyyy')". This is converting the string '30/12/2012' to an internal date object format that is specific to the DB. You can't control the underlying storage format. What you can control however is how that internal date object is converted back to a string by using date formatting functions when you call select.

select to_char(some_date, 'dd/mm/yyyy') my_date from some_table;

In the visual interface you referenced it is simply showing the default date to string conversion.

Upvotes: 1

Colin 't Hart
Colin 't Hart

Reputation: 7729

This is a front-end issue: it's displaying the dates in that format. Because they are date fields, the dates really do represent the dates that you inserted.

I note in the bottom right hand corner of your screenshot that the date there is displayed in MM/DD/YYYY order. Change this setting in Windows and it will more than likely display correctly in your front-end tool.

Upvotes: 2

Related Questions