SQL CREATE and INSERT

Question

CREATE TABLE Application (
    studentID       VARCHAR(10) NOT NULL ,
    firstName       VARCHAR(10),
    lastName        VARCHAR(20),
    ic              VARCHAR(15),
    dateOfBirth     DATE,
    gender          CHAR(1),
    email           VARCHAR(20),
    applStatus      VARCHAR(10),
    homeAddress     VARCHAR(30),
    pinCode         INTEGER,
    country         VARCHAR(10),
    stated          VARCHAR(10),

    primary key (studentID)
);

INSERT INTO APPLICATION VALUES('15WAD04433','DANIEL','CHOO','961019-06-1783','19/10/1996','M','DC@HOTMAIL.COM','SUCCESSFUL','A 4710, LORONG ALOR AKAR 30',25250,'KUANTAN','PAHANG');

Error is [Exception, Error code 30,000, SQLState 22007] The string representation of a date/time value is out of range.

Hi everyone, I am using "NetBeans IDE 8.2" to create a database but when i insert the date 19/10/1996 come out with an error. There is impossible the date of birth is 2016 year same as the current date and time, may i know what to do to set the date of birth back to 1996 without having the error?

This may be of help http://stackoverflow.com/questions/23242000/sqlcode-181-the-string-representation-of-a-datetime-value-is-not-a-valid-dateti — secondbreakfast, Dec 09 '16 at 16:03
never do INSERT INTO my_table values(...) __always__ specifies the columns that you are inserting to like INSERT INTO my_table(col1,col2,..) anway the problem with your date is thtat it should be '1996-10-19' or 1996/10/19 — e4c5, Dec 09 '16 at 16:04
Yes the pattern should be 'YYYY-MM-DD', look here : http://stackoverflow.com/questions/12120433/php-mysql-insert-date-format — Arnaud, Dec 09 '16 at 16:05

score 1 · Accepted Answer · answered Dec 09 '16 at 16:05

From MySQL documentation

Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98')

You need to change your date format 19/10/1996 to 1996-10-19.

score 0 · Answer 2 · answered Dec 09 '16 at 16:03

0

Try to change the date to '10/19/1996' as the pattern is most likely month/day/year. You can also provide the month/day/year pattern for your situation: TO_DATE('19/10/1996', 'DD/MM/YYYY')

answered Dec 09 '16 at 16:03

actc

672
1
9
23

SQL CREATE and INSERT

2 Answers2