INSERT problems: incompatible formats

From SQLZoo
Jump to navigation Jump to search

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
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects