Drop partial dates (YYYY-00-00)

MySQL supports this [1]: MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as '2009-00-00' or '2009-01-00'. If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates. To disallow zero month or day parts in dates, enable strict SQL mode (as of MySQL 5.7.4) or the NO_ZERO_IN_DATE mode (before MySQL 5.7.4). MySQL permits you to store a "zero" value of '0000-00-00' as a "dummy date." This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable strict SQL mode (as of MySQL 5.7.4) or the NO_ZERO_DATE mode (before MySQL 5.7.4). But SQLite does not (although see [2] for notes about how dates are stored there). Sometimes SQLite's partial-date handling is compatible with MySQL's, and sometimes it isn't. For example, strftime doesn't work with partial dates [3]: sqlite> .version SQLite 3.8.7.4 2014-12-09 01:34:36 f66f7a17b78ba617acde90fc810107f34f1a1f2e sqlite> CREATE TABLE dates ( date DATE ); sqlite> INSERT INTO dates VALUES ( '1930-00-00' ); sqlite> INSERT INTO dates VALUES ( '1931-01-01' ); sqlite> SELECT * FROM dates WHERE date < '1931-00-00'; 1930-00-00 sqlite> SELECT * FROM dates WHERE date > '1931-00-00'; 1931-01-01 sqlite> SELECT * FROM dates WHERE date = '1930-00-00'; 1930-00-00 sqlite> SELECT strftime('%Y', date), date FROM dates WHERE date = '1930-00-00'; |1930-00-00 sqlite> SELECT strftime('%Y', date), date FROM dates; |1930-00-00 1931|1931-01-01 PostgreSQL has given out-of-range errors for these partial dates for at least a decade ([4], and I just tested on 9.4.1). Because this is just an intro lesson, I think we should stick to real dates and avoid partial dates. [1]: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html [2]: https://www.sqlite.org/datatype3.html#datetime [3]: https://www.sqlite.org/lang_datefunc.html [4]: http://www.postgresql.org/message-id/4327808A.3090801@freedomcircle.net Subject: Partial dates Date: 2005-09-14 01:44:42
uzinaduzina · Mar 26, 2015 · 554089b · 554089b
1 parent dbd1d59
commit 554089b
Show file tree

Hide file tree

Showing 3 changed files with 7 additions and 7 deletions.
diff --git a/03-filter.md b/03-filter.md
@@ -56,7 +56,7 @@ For example,
 we can ask for all information from the DR-1 site collected since 1930:
 
 ~~~ {.sql}
-SELECT * FROM Visited WHERE (site="DR-1") AND (dated<="1930-00-00");
+SELECT * FROM Visited WHERE (site="DR-1") AND (dated<="1930-01-01");
 ~~~
 
 |ident|site|dated     |

diff --git a/05-null.md b/05-null.md
@@ -42,7 +42,7 @@ Null doesn't behave like other values.
 If we select the records that come before 1930:
 
 ~~~ {.sql}
-SELECT * FROM Visited WHERE dated<"1930-00-00";
+SELECT * FROM Visited WHERE dated<"1930-01-01";
 ~~~
 
 |ident|site|dated     |
@@ -54,7 +54,7 @@ we get two results,
 and if we select the ones that come during or after 1930:
 
 ~~~ {.sql}
-SELECT * FROM Visited WHERE dated>="1930-00-00";
+SELECT * FROM Visited WHERE dated>="1930-01-01";
 ~~~
 
 |ident|site|dated     |
@@ -68,15 +68,15 @@ SELECT * FROM Visited WHERE dated>="1930-00-00";
 we get five,
 but record #752 isn't in either set of results.
 The reason is that
-`null<'1930-00-00'`
+`null<'1930-01-01'`
 is neither true nor false:
 null means, "We don't know,"
 and if we don't know the value on the left side of a comparison,
 we don't know whether the comparison is true or false.
 Since databases represent "don't know" as null,
-the value of `null<'1930-00-00'`
+the value of `null<'1930-01-01'`
 is actually `null`.
-`null>='1930-00-00'` is also null
+`null>='1930-01-01'` is also null
 because we can't answer to that question either.
 And since the only records kept by a `WHERE`
 are those for which the test is true,

diff --git a/07-join.md b/07-join.md
@@ -230,5 +230,5 @@ SELECT rowid, * FROM Person;
 >
 > ~~~ {.sql}
 > SELECT Site.name FROM Site JOIN Visited
-> ON Site.lat<-49.0 AND Site.name=Visited.site AND Visited.dated>='1932-00-00';
+> ON Site.lat<-49.0 AND Site.name=Visited.site AND Visited.dated>='1932-01-01';
 > ~~~