Difference between revisions of "INSERT a date"

From SQLZOO
Jump to: navigation, search
(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-mysql" style="display: none">Oracle can be made to accept almost any format if we use the TO_DATE function. Details at
+
<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 10: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


Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense