How to create table with default DATE column using { ts }?

Question

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

create table my_table ( 
 transaction_date DATE DEFAULT { ts '1900-01-01 00:00:00' } NULL 
)

error --

ORA-00911: invalid character
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than
 letters and numbers. $#_ are also allowed after the first
 character. Identifiers enclosed by doublequotes may contain
 any character other than a doublequote. Alternative quotes
 (q'#...#') cannot use spaces, tabs, or carriage returns as
 delimiters. For all other contexts, consult the SQL Language
 Reference Manual.

As the above create statement is generated by a third-party software, what's the correct syntax without using date or to_date()?

Answer 1

Whatever syntax that is, that is not correct in Oracle.

Use this:

create table my_table ( 
 transaction_date DATE DEFAULT timestamp'1900-01-01 00:00:00' NULL 
);

• Balazs Papp: @ypercubeTM It would, but OP asked for without using date or to_date().