Marek: Created page with "Can't insert data because of incompatable formats.

schema:scott

If the data inserted is of the wrong type the insert may fail. B..."

2012-07-17T11:13:34Z

Created page with "Can't insert data because of incompatable formats. <div class='ht'> <div class=params>schema:scott</div> <div> If the data inserted is of the wrong type the insert may fail. B..."

New page

Can't insert data because of incompatable formats.
<div class='ht'>
<div class=params>schema:scott</div>
<div>
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).
</div>

<source lang=sql class='tidy'>DROP TABLE t_x</source>
<source lang=sql class='setup'></source>

<source lang='sql' class='def'>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
</source>
<source lang='sql' class='def e-sqlserver'>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
</source>
<source lang='sql' class='def e-access'>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
</source>

<div class="ecomm e-access" style="display: none">The string 'abcdef' is accepted but truncated. The date
'10-11-12' is accepted (as UK format).</div>
<div class="ecomm e-oracle" style="display: none">The string 'abcdef' is reject - too long for the
VARCHAR(5), '10-11-12' is rejected as a date</div>
<div class="ecomm e-postgres" style="display: none">The string 'abcdef' is accepted - but truncated. The date
'10-11-12' is accepted (as a UK format).</div>
<div class="ecomm e-mysql" style="display: none">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</div>
<div class="ecomm e-sqlserver" style="display: none">The string 'abcdedf' is rejected (too long). The date
'10-11-12' is accepted (as US format).</div>
</div>

{{INSERT and DELETE ref}}

INSERT problems: incompatible formats - Revision history

Marek: Created page with "Can't insert data because of incompatable formats. schema:scott If the data inserted is of the wrong type the insert may fail. B..."

Marek: Created page with "Can't insert data because of incompatable formats.

schema:scott

If the data inserted is of the wrong type the insert may fail. B..."