Difference between revisions of "INSERT a date"
From SQLZOO
(Created page with "Insert a date. <div class='ht'> <div class=params>schema:scott</div> <div> The format of dates can be very tricky. Most implementations are fairly robust in what will be accep...") |
|||
| Line 62: | Line 62: | ||
"30 days hath September..."</div> | "30 days hath September..."</div> | ||
| − | <div class="ecomm e- | + | <div class="ecomm e-oracle" style="display: none">Oracle can be made to accept almost any format if we use the TO_DATE function. Details at |
[http://oradoc.photo.net/ora817/DOC/server.817/a85397/sql_elem.htm#34926 Date Format Elements]</div> | [http://oradoc.photo.net/ora817/DOC/server.817/a85397/sql_elem.htm#34926 Date Format Elements]</div> | ||
</div> | </div> | ||
{{INSERT and DELETE ref}} | {{INSERT and DELETE ref}} | ||
Latest revision as of 09:14, 18 July 2012
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
INSERT a date
