Datetime data type in oracle?

Question

I have to migrate tables from oracle to redshift, I have to create a script, I need to know that Can we use Datetime as a datatype instead of using Timestamp or date in oracle?

Answer 1

No, datetime is not a recognised data type in Oracle.

The valid data types are listed in the documentation, which has a section on datetime and interval data types:

The datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. Values of datetime data types are sometimes called datetimes.

Some ANSI and other data types are supported for compatibility, but there is no datetime type.

This is easy to check of course:

create table t42 (x datetime);

ORA-00902: invalid datatype

But if you're writing a script to migrate from Oracle to somewhere else, you presumably don't want to be modifying the source Oracle schema anyway; you need your script to translate DDl and DML from Oracle syntax to the target DB syntax. Or you may find tools already exist (Amazon seems to have its own tool, for instance, if your source DB is suitable), or guidance from others who have done something similar in the past on blogs, etc.