Unable to insert date and time when using date datatype

Question

I am hitting a bit of a problem when using the date datatype. When trying to save a row to the table where the field it throws an error ora 01830 and complains about converting the date format picture ends...etc. Now when I do the insert, I use the to_date function with the format of "dd-mon-yyyy hh24:mi:ss". Of course, when I remove the time element, everything is perfect.

Checking sysdate, I noticed that the time element wasn't be displayed, and I used alter session set nls_date_format to set the date and time I want to save to the table, which worked!

I used alter system set nls_date_format ="dd-mon-yyyy hh24:mi:ss" scope=spfile; This showed that it was altered, and I can see the setting in the enterprise management console. In sqlplus, I shutdown the database, and restarted with startup mount; alter database open; and then selecting sysdate, it still shows the date as dd-mon-yy, and still no time! Checking the enterprise management, and looking up the nls_date_format the setting is still shown as "dd-mon-yyyy hh24:mi:ss".

So, my question is this - what am I doing wrong? Why can't save date and time using date in Oracle 11g?????

Thanks

What is the data type of the column in the table you are writing to? I'm assuming it is a DATE. What is the data type of the data you are trying to write to the table? Are you trying to write a DATE to the table (i.e. the result of a call to the SYSDATE function)? Or are you trying to take a VARCHAR2, convert it to a DATE, and write that to the table? And is the concern about writing the data or displaying the data? — Justin Cave
Can you show us the to_date() call ? include the input of the varchar2 date — A.B.Cade

Glenn Glenn · Accepted Answer · 2012-01-19T01:49:55

Dates are stored with "second" granularity in Oracle.

Display formats are dependent on the system and session. In your case, since you are connecting with sqlplus, you are using a default session format from the client that does not include time. You need to execute an:

ALTER SESSION SET nls_date_format ="dd-mon-yyyy hh24:mi:ss";

when you start up your sqlplus client in order to change the default display. There is a client side file (glogin.sql?) that sqlplus will run on startup. You can place this kind of command in there if you want it to be executed each you start that client. I'm pretty sure the sqlplus client sends an "alter session set nls_date..." on start up.

In general, when outputting dates, I think it is better to just be explicit on the format by doing a TO_CHAR(myDateColumn, "dd-mon-yyyy hh24:mi:ss"). If you are reading dates programatically, you don't need to worry about it since you are dealing with internal formats, not display formats.

Unable to insert date and time when using date datatype

2 Answers