CODEWITHSUNDEEP
sqloracle-databasedatetimeoracle-sqldevelopersql-insert
ChloeJ7
ChloeJ7

Reputation: 23

Oracle SQL Developer data format

I have created a table 'USER' in Oracle SQL Developer.

CREATE TABLE USER(
USER_ID INTEGER NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL, 
LAST_NAME VARCHAR2(50) NOT NULL,
ID_CODE INTEGER NOT NULL, 
DATE_OF_BIRTH TIMESTAMP NOT NULL,
TRAINING_CLUB VARCHAR2(50) NOT NULL, 
HAVE_LICENCE NUMBER(1) NOT NULL,
AGE_GROUP VARCHAR2(3) NULL, 
SPECIALITY VARCHAR2(100) NOT NULL,
COMMENT VARCHAR2(200) NULL,
CONSTRAINT USER_PK PRIMARY KEY (USER_ID)
);

Now I'd like to insert data in it, but it will only accept date which is 2000 or more and when it's less than 2000, for example '1998-13-07', it shows not a valid month.

INSERT INTO USER(USER_ID, FIRST_NAME, LAST_NAME, ID_CODE, DATE_OF_BIRTH, TRAINING_CLUB, HAVE_LICENCE, AGE_GROUP, SPECIALITY) VALUES (1, 'Mari', 'Mets', 4990713134, '1999-13-07', 'Erki Noole Athletics club', 1, 'U23', 'Sprint, hurdle race' );
INSERT INTO USER(USER_ID, FIRST_NAME, LAST_NAME, ID_CODE, DATE_OF_BIRTH, TRAINING_CLUB, HAVE_LICENCE, AGE_GROUP, SPECIALITY) VALUES (2, 'Meelis', 'Valgepea', 39704230213, '1997-23-04', 'Runningpartner', 1, 'M', 'Middle- ja long distance running');
INSERT INTO USER(USER_ID, FIRST_NAME, LAST_NAME, ID_CODE, DATE_OF_BIRTH, TRAINING_CLUB, HAVE_LICENCE, AGE_GROUP, SPECIALITY) VALUES (3, 'Karina', 'Justinov', 6020330872, '2002-30-03', 'SK Fortis', 1,'U20','Long and high jump');
INSERT INTO USER(USER_ID, FIRST_NAME, LAST_NAME, ID_CODE, DATE_OF_BIRTH, TRAINING_CLUB, HAVE_LICENCE, AGE_GROUP, SPECIALITY) VALUES (4, 'Lewis', 'Kordon', 38711120678, '1987-12-11', 'Niidupargi Athletics club', 1, 'M', 'Shot put');
INSERT INTO USER(USER_ID, FIRST_NAME, LAST_NAME, ID_CODE, DATE_OF_BIRTH, TRAINING_CLUB, HAVE_LICENCE, AGE_GROUP, SPECIALITY) VALUES (5, 'Getter', 'Tihhikov', 49802280417, '1998-28-02', 'Trainingpartner', 1, 'W','Long distance running');
INSERT INTO USER(USER_ID, FIRST_NAME, LAST_NAME, ID_CODE, DATE_OF_BIRTH, TRAINING_CLUB, HAVE_LICENCE, AGE_GROUP, SPECIALITY) VALUES (6, 'Andres', 'Allikvee', 50603212165, '2006-21-03', 'SK Lindon', 1, 'U16', 'Pole vault');

It shows that row 3 and 6 will be added and 1, 2, 4, 5 have not valid month.

Upvotes: 1

Views: 65

Answers (1)

GMB
GMB

Reputation: 222612

create table users(
    user_id integer not null,
    date_of_birth timestamp not null
);

insert into users values (1, '1999-13-07');
-- ORA-01843: not a valid month

The problem is with the date literal. We are giving a literal string for a timestamp column, so how this is interpreted depends on database settings.

We can either explictly turn the strings to timestamps, providing the proper format specification:

into users values (1, to_timestamp('1999-13-07', 'YYYY-DD-MM'));
-- 1 rows affected

Or we can change the default timestamp format for our current session, so strings are correctly implicitly converted:

alter session set nls_timestamp_format = 'YYYY-DD-MM';
insert into users values (2, '1999-13-07');
-- 1 rows affected

Demo on DB Fiddle

Upvotes: 2

Related Questions