Marek at 09:14, 18 July 2012

2012-07-18T09:14:24Z

Marek: Created page with "Insert a date.

schema:scott

The format of dates can be very tricky. Most implementations are fairly robust in what will be accep..."

2012-07-17T10:44:58Z

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..."

New page

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 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.
</div>

<source lang=sql class='tidy'>DROP TABLE t_x</source>
<source lang=sql class='setup'>CREATE TABLE t_peep
(id INTEGER PRIMARY KEY
,name VARCHAR(50))</source>

<source lang='sql' class='def e-db2'>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
</source>
<source lang='sql' class='def e-access'>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
</source>
<source lang='sql' class='def e-mysql'>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
</source>
<source lang='sql' class='def e-oracle'>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
</source>
<source lang='sql' class='def e-sqlserver'>CREATE TABLE t_x (x VARCHAR(5), y DATETIME);
INSERT INTO t_x VALUES ('Ryka','01 Mar 1997');
SELECT * FROM t_x
</source>
<source lang='sql' class='def'>CREATE TABLE t_x (x VARCHAR(5), y DATE);
INSERT INTO t_x VALUES ('Ryka', DATE '1997-03-01');
SELECT * FROM t_x
</source>

<div class="ecomm e-sqlite" style="display: none">All data types are ignored, simple strings are used.
Given this you should use 'yyyy-mm-dd', your dates will at least be
ordered.
</div>
<div class="ecomm e-access" style="display: none">When used within VBA Access likes dates to be enclosed in hash signs...

#20 May 1962#</div>
<div class="ecomm e-mysql" style="display: none">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..."</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
[http://oradoc.photo.net/ora817/DOC/server.817/a85397/sql_elem.htm#34926 Date Format Elements]</div>
</div>

{{INSERT and DELETE ref}}

@@ Line 62: / Line 62: @@
 "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>
 </div>
 {{INSERT and DELETE ref}}

INSERT a date - Revision history

Marek at 09:14, 18 July 2012

Marek: Created page with "Insert a date. schema:scott The format of dates can be very tricky. Most implementations are fairly robust in what will be accep..."

Marek: Created page with "Insert a date.

schema:scott

The format of dates can be very tricky. Most implementations are fairly robust in what will be accep..."