Oracle Database Discussions

Hi

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!

Then based on advice in a previous thread to permanently fix the problem, 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 em. 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 em, 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