INSERT a date

Insert a date.

schema:scott

The format of dates can be very tricky. Most implementations are fairly robust in what will be accepted - but a date such as 01-02-03 might be interpreted in different ways in different countries.

Using a three character month and a four digit year in single quotes (such as '01 Mar 1997') works safely with most systems other than MySQL.

DROP TABLE t_x

CREATE TABLE t_peep
 (id INTEGER PRIMARY KEY
 ,name VARCHAR(50))

CREATE TABLE t_x (x VARCHAR(5), y DATE);
DELETE FROM t_x;
INSERT INTO t_x VALUES ('Ryka', '1997-03-01');
SELECT * FROM t_x

CREATE TABLE t_x (x VARCHAR(5), y DATETIME);
INSERT INTO t_x VALUES ('Ryka','01 Mar 1997');
INSERT INTO t_x VALUES ('Olly',#25 May 1985#);
SELECT * FROM t_x

CREATE TABLE t_x (x VARCHAR(5), y DATE);
INSERT INTO t_x VALUES ('Ryka','1997-03-01');
INSERT INTO t_x VALUES ('Impos','1997-09-31');
SELECT * FROM t_x

INSERT INTO t_x VALUES ('Ryka','01 Mar 1997');
INSERT INTO t_x VALUES ('Milly', 
   TO_DATE('July 9, 1995', 
           'Month dd, YYYY, HH24:MI'));
SELECT * FROM t_x

CREATE TABLE t_x (x VARCHAR(5), y DATETIME);
INSERT INTO t_x VALUES ('Ryka','01 Mar 1997');
SELECT * FROM t_x

CREATE TABLE t_x (x VARCHAR(5), y DATE);
INSERT INTO t_x VALUES ('Ryka', DATE '1997-03-01');
SELECT * FROM t_x

All data types are ignored, simple strings are used.

Given this you should use 'yyyy-mm-dd', your dates will at least be ordered.

When used within VBA Access likes dates to be enclosed in hash signs...

1. 20 May 1962#

MySQL only accepts dates in the format yyyy-mm-dd.

Be warned that it will accept dates that don't exist - such as 31st September.

"30 days hath September..."

Oracle can be made to accept almost any format if we use the TO_DATE function. Details at Date Format Elements

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