YYYYMMDD date format in WHERE clause on 4.0.13

There seems to be a problem with supplying dates in the YYYYMMDD format in
the WHERE clause of a query, unless the value is placed in quotes
('YYYYMMDD'). The problem only seems to appear in WHERE clauses; INSERTing
and UPDATEing with the numeric format works fine.

I'm running MySQL 4.0.13 installed from the .dmg packages from mysql.com on
a PowerBook running Mac OS X 10.2.6 (6L60).

Here's a set of commands to illustrate the issue:

------------------------------------------------------------ ------------
mysql> use test;
mysql> create table test (d datetime);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into test values (20030610),(20030611);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test;
+---------------------+
| d |
+---------------------+
| 2003-06-10 00:00:00 |
| 2003-06-11 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from test where d < 20030611;
Empty set (0.00 sec)

mysql> select * from test where d < 20030615;
Empty set (0.00 sec)

mysql> select * from test where d < '20030615';
+---------------------+
| d |
+---------------------+
| 2003-06-10 00:00:00 |
| 2003-06-11 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> update test set d = 20030101;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from test;
+---------------------+
| d |
+---------------------+
| 2003-01-01 00:00:00 |
| 2003-01-01 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)
------------------------------------------------------------ ------------

---
Barney Boisvert, Senior Development Engineer
AudienceCentral
bboisvert@audiencecentral.com
voice : 360.756.8080 x12
fax : 360.647.5351

www.audiencecentral.com
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 6/10/2003


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: YYYYMMDD date format in WHERE clause on 4.0.13

Barney Boisvert writes:
> There seems to be a problem with supplying dates in the YYYYMMDD format in
> the WHERE clause of a query, unless the value is placed in quotes
> ('YYYYMMDD'). The problem only seems to appear in WHERE clauses; INSERTing
> and UPDATEing with the numeric format works fine.
>
> I'm running MySQL 4.0.13 installed from the .dmg packages from mysql.com on
> a PowerBook running Mac OS X 10.2.6 (6L60).
>
> Here's a set of commands to illustrate the issue:
>

[skip]

>
> ---
> Barney Boisvert, Senior Development Engineer
> AudienceCentral
> bboisvert@audiencecentral.com
> voice : 360.756.8080 x12
> fax : 360.647.5351
>
> www.audiencecentral.com
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.489 / Virus Database: 288 - Release Date: 6/10/2003

Hi!

This is a known issue and is partially explained in our manual:

* `DATE'/`DATETIME' checking is now a bit stricter to support the
ability to automatically distinguish between date, datetime, and
time with microseconds. For example, dates of type `YYYYMMDD
HHMMDD' are no longer supported; one must either have separators
between each `DATE'/`TIME' part or not at all.


We are now working on further improving a manual on the issue, plus on
returning the error message instead of the empty set.

Problem actually is not in the quotes, as much as it is in the fact
that a column is DATETIME. Adding siz zeros to the end of integer
would make a query work.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org