INSERT problems: incompatible formats

Can't insert data because of incompatable formats.

schema:scott

If the data inserted is of the wrong type the insert may fail. Because the date format may depend on the local settings this may cause confusion. Using a 4 digit year and a three character month works for most systems (in English speaking regions).

DROP TABLE t_x

CREATE TABLE t_x (x VARCHAR(5), y DATE);
INSERT INTO t_x VALUES ('abcdef', NULL);
INSERT INTO t_x VALUES ('ambig', '10-11-12');
INSERT INTO t_x VALUES ('unamb', '10 Nov 2012');
SELECT * FROM t_x

CREATE TABLE t_x (x VARCHAR(5), y DATETIME);
INSERT INTO t_x VALUES ('abcdef', NULL);
INSERT INTO t_x VALUES ('ambig', '10-11-12');
INSERT INTO t_x VALUES ('unamb', '10 Nov 2012');
SELECT * FROM t_x

CREATE TABLE t_x (x VARCHAR(5), y DATETIME);
INSERT INTO t_x VALUES ('abcdef', NULL);
INSERT INTO t_x VALUES ('ambig', '10-11-12');
INSERT INTO t_x VALUES ('unamb', '10 Nov 2012');
SELECT * FROM t_x

The string 'abcdef' is accepted but truncated. The date '10-11-12' is accepted (as UK format).

The string 'abcdef' is reject - too long for the VARCHAR(5), '10-11-12' is rejected as a date

The string 'abcdef' is accepted - but truncated. The date '10-11-12' is accepted (as a UK format).

The string 'abcdef' is accepted but truncated. The date

'10-11-12 is accepted as 12th Nov 2010, the date '10 Nov 2012' is not

recognised - instead the New Year's day 1 BC is used

The string 'abcdedf' is rejected (too long). The date '10-11-12' is accepted (as US format).

INSERT and DELETE Operations

INSERT new records

UPDATE existing records

DELETE records

INSERT ... SELECT

INSERT: Not all fields need to be specified

INSERT a date

Explicitly enter a NULL

INSERT problems: reference

DELETE problems: reference

INSERT problems: incompatible formats

String contains a quote '

Data normalization