I am starting SQL, but the query is not running. I get an error "ORA-00907: missing right parenthesis" [closed]

Question

create table myData
(
    My_Id number(5),
    My_Name varchar2(50),
    My_DOB date(10),
    My_Email varchar2(50)
);

What is wrong with this query? When I run this query, the table is not created.

Answer 1

Oracle's data type DATE has no precision. Despite its name, though, it is not a mere date, but a datetime, i.e. it consists of a date part and a time part. When we want to store a date alone, we set the time part to midnight (00:00:00).

You tried to give the date column a precision of 10. I suppose you had a date with two digits for a day, two for a month and four for a year plus two delimiters in mind. As mentioned, DATEallows for a time, too. When you want to avoid this there are three options:

1. You simply rely on your code to never put a datetime with a time part into this column. If you want to write today's date for instance, you would not write Oracle's SYSDATE, because this is the current timestamp including hours minutes and seconds, but TRUNC(SYSDATE), which is the mere date.
2. You add a check constraint to throw an exception whenever someone tries to insert a date with a time.
3. You write a before insert/update trigger, where you remove the time part with TRUNC, before the date is written to the table.

Here is option #2:

CREATE TABLE mydata
(
  my_id      NUMBER(5)     GENERATED ALWAYS AS IDENTITY,
  my_name    VARCHAR2(50)  NOT NULL,
  my_dob     DATE          NOT NULL,
  my_email   VARCHAR2(50),
  CONSTRAINT chk_dob_date_only CHECK (my_dob = trunc(my_dob))
);

At last I'd like to add here, that a date has no format in the database. You write it using a date literal, e.g. update mydata set my_dob = DATE '2000-01-15' where my_id = 123. And when you select it, e.g. select my_dob from mydata where my_id = 123, you see the date formatted by your tool or app, and this can be '15.2.22' or '02/15/2022' or any other format. You can convert the date to a formatted string in SQL using TO_CHAR, but you would usually not do this, but rather have your app do it using the user's environments settings. Thus the same query works for all users and each gets the date shown in the format they are accustomed to.

Answer 2

Use this code:

CREATE TABLE myData (
My_Id INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
My_Name VARCHAR(50),
My_Email VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)