What is the separator or It can be anything ?

[Mahtab Alam]

I created a table as

create table TT(time timestamp(6));

Now I want to insert row in this table and I don`t want to use to_timestamp function with format_model to do this.
So I checked the timestamp_format from the view v$nls_parameters

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM


insert into TT values('12-MAR-13 12.10.11.234567');

It inserted one row because the pattern of character string '12-MAR-13 12.10.11.234567' matches to that of DD-MON-RR HH.MI.SSXFF AM.

Now I tried some variation

insert into TT values('12-MAR-13 12:10:11.234567');

It worked , but I thought It will not work as I have used colon rather than period symbol to separate hour,minute and second.

insert into TT values('12-MAR-13 12-10-11.234567');

This also works ,again it works I thought It will not work for same reason (using hyphen in place of period).

insert into TT values('12:MAR:13 12-10-11.234567');

This also works.

insert into TT values('12-MAR-13 12:10:11:234567');

But this does not work.

Can you tell how and when variation is allowed and when you have to be dead right.

Shukran

[Martin Vashko]

In general, I'm not sure what your problem actually is. Why not to stick to the format that exactly matches the NLS settings? That one cannot fail (I'd say).

In particular, however, the decision to assume the NLS settings you happen to have just now is not a wise one. If the NLS settings changes for whatever reason, your script(s) stops to work. There are many many many questions on AskTom, for example, that all boil down to an assumption of a specific NLS settings that has subsequently changed.

At the very least, you should set the chosen NLS format for timestamp at the beginning of your script if you don't want to specify it explicitly everywhere. Using to_timestamp with explicit format is still the best solution, however, as maintaining SQL scripts that may change the NLS settings according to their needs can become difficult if you happen to have more of them and call one from another, for example.