Varchar2 datatype allows systimestamp

Question

I have one table with varchar2 datatypes like below

create table t11 (aa varchar2(100),bb varchar2(100));

Now, if I trying to insert systimestamp into above, values are getting inserted:

insert into t11 values (systimestamp,systimestamp);
commit;

Question is why this conversion is allowed in Oracle.

I am using Oracle 11g.

Answer 1

Your column type is varchar2 and the return type of systimestamp is timestamp. timestamp can not be stored directly into varchar2 column, Oracle implicitly convert the timestamp value into varchar2 with the rule specified in the init parameter, NLS_TIMESTAMP_FORMAT.

You can read Data Conversion Rules and NLS_TIMESTAMP_FORMAT for more detail.

Answer 2

Oracle have Implicit Data Conversion Read more about here that depends on NLS_DATE_FORMAT

Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter