Hello out there,
I don't know if that has been reported already. So I'm doing it again.
I have a table containing a date column and set my NLS_DATE_FORMAT to 'DD.MM.YYYY HH24:MI:SS'.
In version 3.2, I could enter date values just by giving a date without time e.g. 29.08.2013. In the table editor of version 4.0, I'm forced to enter 29.08.2013 00:00:00 otherwise the field is cleared after leving it by pressing TAB.
Is there some new preference setting for that? I'm having another table where I want to enter date values with time though...
If that matters, I'm using SQL*Developer 126.96.36.199.27 with JDK 7.0_17 64bit on OpenSuse Linux 12.3 and connecting to Oracle 188.8.131.52.0 on Solaris 10 Sparc64.
As I'm writing the JDK version, could it be that a newer JDK solves that issue? I'll try that.
>>In the table editor of version 4.0, I'm forced to enter 29.08.2013 00:00:00 otherwise the field is cleared after leving it by pressing TAB.
The Date editor defaults the time to the current time for a new record and displays the stored time for an existing value.
How are you forced to input '00:00:00' for the date?
I can reproduce this issue, using SQL Developer 4.0EA1 and Java 1.7.0_25. My locale is set to en_US, and the date format (set via SQL Developer preferences) is 'YYYY-MM-DD HH24:MI:SS'.
If I enter "2013-09-10" into a date column and then press TAB, the field is simply cleared. If I enter "2013-09-10 00:00:00", on the other hand, the value is apparently accepted and left as-is.
OK, I see what you're saying now, mostly.
If you set your nls date format to not show the time, it will work as you want.
If you enter an incomplete date string, which is what you're doing with your NLS DATE FORMAT, the parser doesn't understand your data. So it doesn't take.
Well, then the parser lost some intelligence in the new version.
In 3.2 I could enter incomplete date values as well as complete ones. I need both, since in one case I want to enter just the date and in another case I also want to enter the time. And I don't want to change my NLS settings every time.
By the way, just the date is accepted as date literal by SQL like this:
insert into test values ('21.08.2013');
This works even if my NLS_DATE_FORMAT includes the time.
I'm thinking that it should assume all zeros for the time portion in this scenario, since that's how sqlplus (and, I believe, previous SQL Developer releases) handles it. For example:
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> select to_date('2013-09-10') from dual;
The problem is in version 3.2 the field was treated as a string, now we recognize it as a date and treat it as such. When you use the insert with '', you're asking the database to implicitly convert the string to a date.
I think we can make the change to have our cake and eat it too, but probably not until version 4.NEXT. So for now, you'll need to input complete date values, change your NLS, OR use the date editor (click on the pencil) to handle that for you.