PL/SQL Date Insert

Question

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'))

Answer 1

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.

Answer 2

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.

Answer 3

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.