From: Daevid Vincent
Date: April 15 2003 9:28pm
Subject: How do I compare date part of datetime with CURRENT_DATE?
List-Archive: http://lists.mysql.com/mysql/137353
Message-Id: <002601c30395$ef702360$a50aa8c0@Locutus>
MIME-Version: 1.0
Content-Type: text/plain;	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

How would I go about comparing the date portion (not the time) of a =
DATETIME
field with CURRENT_DATE?? Basically if given the data below, I want the
'alarm for today' to be returned since that occurs today (at sometime =
that I
don't care about). My feeling is that CURRENT_DATE uses a time of =
12:00:00
or something and mySQL doesn't know to ignore the time portions when
comparing against CURRENT_DATE. This to me seems like a bug (although =
I'm
sure it's by design), as I would expect it to work the way *I* want it =
to
work. If I cared about the time, I would use NOW() instead.

None of these worked:

mysql> select CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2003-04-15   |
+--------------+

mysql> SELECT alarm_notes, alarm_date, DATE_FORMAT(alarm_date,'%m/%d/%y
%h:%i %p') AS alarm_date_format FROM alarm_table WHERE
alarm_contact_table_id IS NULL AND alarm_date =3D CURRENT_DATE AND
alarm_rep_table_id =3D '1' ORDER BY alarm_date;

Empty set (0.00 sec)

mysql> SELECT alarm_notes, alarm_date, DATE_FORMAT(alarm_date,'%m/%d/%y
%h:%i %p') AS alarm_date_format FROM alarm_table WHERE
alarm_contact_table_id IS NULL AND alarm_date >=3D CURRENT_DATE AND
alarm_rep_table_id =3D '1' ORDER BY alarm_date;
+---------------------------------------+---------------------+----------=
---
------+
| alarm_notes                           | alarm_date          |
alarm_date_format |
+---------------------------------------+---------------------+----------=
---
------+
| alarm for today                       | 2003-04-15 16:00:00 | 04/15/03
04:00 PM |
| alarms default to 14 days from today. | 2003-04-17 10:00:00 | 04/17/03
10:00 AM |
| the year 2004                         | 2004-12-01 10:00:00 | 12/01/04
10:00 AM |
+---------------------------------------+---------------------+----------=
---
------+
3 rows in set (0.00 sec)

mysql> SELECT alarm_notes, alarm_date, DATE_FORMAT(alarm_date,'%m/%d/%y
%h:%i %p') AS alarm_date_format FROM alarm_table WHERE
alarm_contact_table_id IS NULL AND alarm_date >=3D CURRENT_DATE AND =
alarm_date
< (CURRENT_DATE+1) AND alarm_rep_table_id =3D '1' ORDER BY alarm_date;

ERROR 1064: You have an error in your SQL syntax near '+1) AND
alarm_rep_table_id =3D '1' ORDER BY alarm_date' at line 1

mysql> SELECT alarm_notes, alarm_date, DATE_FORMAT(alarm_date,'%m/%d/%y
%h:%i %p') AS alarm_date_format,=20
DATE_FORMAT(alarm_date,'%Y-%m-%d') AS adate FROM alarm_table WHERE
alarm_contact_table_id IS NULL AND adate =3D CURRENT_DATE AND
alarm_rep_table_id =3D '1' ORDER BY alarm_date;

ERROR 1054: Unknown column 'adate' in 'where clause'