CODEWITHSUNDEEP
sqloracle-database
user3077239
user3077239

Reputation: 135

Create table with getdate() function

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

Upvotes: 4

Views: 21138

Answers (3)

Abhishek Singh Kunwar
Abhishek Singh Kunwar

Reputation: 11

Since we do not need to write NOT NULL as it accepts default function.

You can try this:

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

Upvotes: 1

MT0
MT0

Reputation: 168281

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

Upvotes: 3

Filipe Silva
Filipe Silva

Reputation: 21657

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

Upvotes: 2

Related Questions