Create table with getdate() function

Question

Trying to create table that have a getdate() function

CREATE TABLE Orders 
(OrderId int NOT NULL PRIMARY KEY, 
 ProductName varchar(50) NOT NULL, 
 OrderDate datetime NOT NULL DEFAULT GETDATE() 
);

but got an error

ora-00901 right parenthesis missing

Answer 1

You need to use:

• The DATE data type (which contains years through seconds data);
• The current date/time in oracle is retrieved using SYSDATE;
• The DEFAULT needs to go before the constraint; and
• Although VARCHAR works and is currently synonymous with VARCHAR2, you probably want to use VARCHAR2 for a string data type.

Like this:

CREATE TABLE Orders 
(
   OrderId     INT          NOT NULL
                            PRIMARY KEY, 
   ProductName VARCHAR2(50) NOT NULL, 
   OrderDate   DATE         DEFAULT SYSDATE
                            NOT NULL  
);

Answer 2

Try doing this instead:

CREATE TABLE Orders (
  OrderId INT NOT NULL PRIMARY KEY,
  ProductName VARCHAR(50) NOT NULL,
  OrderDate DATE DEFAULT sysdate NOT NULL
  );

• Changed DATETIME to DATE
• Changed GETDATE() to sysdate
• Moved the NOT NULL after the DEFAULT

sqlfiddle demo