Oracle – How to Create Table with Default DATE Column Using { ts }

oracle

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()?

Best Answer

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 
);

Related Solutions

How to create an UTC index from a ‘DATE’ typed column with Oracle

To be precise, the date type stores timestamps without any timezone information rather than "in local time zone".

How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?

You have three options:

index the date and convert your timestamp to date when querying, eg:

select * from foo where foo_date=cast(systimestamp as date) from dual;

create a functional index on the date converted to a timestamp with local time zone, eg:

create unique index i_foo on foo(cast(ts as timestamp with local time zone));
select * from foo where cast(ts as timestamp with local time zone)=
                        cast(X as timestamp with local time zone);

change the type of your columns of type date to timestamp with time zone or timestamp with local time zone

Of these, it is very likely that the last option is the best one - timestamp with local time zone is the 'correct' type for most data that refers to a fixed point in time. (As opposed to 'noon' which is always 12:00 no matter which time zone you are in.)

Oracle, Create table with inline constraint declarations using a different tablespace

Like this:

create table THIS_TABLE (
  id number NOT NULL, 
  constraint THIS_TABLE_PK PRIMARY KEY(id) 
             USING INDEX TABLESPACE INDEX_TABLESPACE
) tablespace DATA_TABLESPACE;

USING INDEX TABLESPACE is the syntax - you weren't far off.

As far as good/bad practice is concerned, that's opinion-based, so not really something that should be asked here. The alternative is to use the ALTER TABLE .... syntax after table creation.

Best Answer

Related Solutions

How to create an UTC index from a ‘DATE’ typed column with Oracle

Oracle, Create table with inline constraint declarations using a different tablespace

Related Question