Search code examples
sqloracle-databasedatesyntaxalter

date syntax issue in sql

I keep getting this error message when trying to change the data type of my column:

alter table x modify column order_date date NOT NULL;

ERROR at line 1

ORA-00905 missing keyword

I not sure where I am going wrong, as I am aware there are many types of dates in sql?

Many thanks

Solution

  • The MODIFY clause does not take COLUMN as a keyword. This will work:

    alter table x modify order_date date NOT NULL;

    The syntax is documented in the Oracle SQL reference. Find out more.

    We only need to include COLUMN with commands which have several different possibilities. For instance, with the ALTER TABLE ... DROP command, because we can drop columns, constraints or partitions....

    alter table x drop column order_date ;

    "when I tried entering NOT NULL, it said the table needed to be empty"

    You should be able to apply a NOT NULL constraint, providing all the rows in the table have a value in the order_date column. The error message you get is quite clear:

    ORA-01758 table must be empty to add mandatory (NOT NULL) column

    This means your column has some rows without values. So, you need to update the table and populate those rows with some value; what you will use as a default depends on your business rules.