If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

what you need to understand is that both dates and timestamps are internally stored as numbers - a value of the number of days and fractional days since a specific point in time. if used in a query, they are to_char() automatically with a certain format, if inserted, they are to_date() or to_timestamp() also with that certain format. I dont know if enough of the internals are revealed to e.g. allow you to insert the number 1.5 into a date column (thereby giving you a date that is 1.5 days after the epoch) so all insertion into, modification of and selection from date and time type columns must be done with sending in a representation of the time in format X and addionally a description of that format X in oracle compliant formatting

The issue was resolved by also setting NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-HH24.MI.SS.FF';

Otherwise get the error:
SQL> INSERT INTO ADMIN.RESUEST VALUES(1, 1, DEFAULT, 1, DEFAULT, DEFAULT);
INSERT INTO ADMIN.RESUEST VALUES(1, 1, DEFAULT, 1, DEFAULT, DEFAULT)
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SQL>

indeed... you can set the system wide default format for a timestamp to be that.. but bear in mind then that oracle will only thus go and do an implicit TO_CHAR or TO_TIMESTAMP using that format string..

In all programming situations, it pays to be explicit rather than implicit; assuming a computer can think gets you into trouble.

additionally, when deviating from the default values, you should be aware that your change may break other code that also does its work implicitly with the old format. If you ahve a legacy app that is inserting or selecting timestamp data and is expecting it to be in some particular format, you potentially just broke it

The solution? Dont change defaults. Dont rely on implicit conversions. Be thorough now to avoid debugging later

As cjard said, you have to understand that dates and timestamps are stored in an internal format, NOT STRINGS, strings are just for DISPLAY. SO ALWAYS CONVERT EXPLICITLY :

Thanks for the input.

The idea is to maintain the storage/display formats of TIMESTAMP - externally - without change to already existing application code, which is to be moved to work on Oracle. I'm not concerned about how it is stored internally. The timestamp format expected when doing a SELECT or INSERT is 2006-08-21-16.37.30.459999.

The 'definition of the table can be altered to a certain extent with respect to defaults' and so can the 'session settings' but code is to be changed only if the previous 2 cannot handle the requirement.

Given this requirement, the issue can be resolved only by use of session applicable code as follows:

ALTER SESSION might match your needs for now, but it is a very bad habit IMO to rely on the session date format. I'm pretty sure you will regret it in the long term. So as a short-term solution that can be OK, but keep in mind that this should be changed in the future.

What we do in order to use the same format everywhere we need to is using a date format as a package variable and use it instead of hard-coded string format. For example :