List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 15 2003 9:28pm
Subject:How do I compare date part of datetime with CURRENT_DATE?
View as plain text  
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 = CURRENT_DATE AND
alarm_rep_table_id = '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 >= CURRENT_DATE AND
alarm_rep_table_id = '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 >= CURRENT_DATE AND alarm_date
< (CURRENT_DATE+1) AND alarm_rep_table_id = '1' ORDER BY alarm_date;

ERROR 1064: You have an error in your SQL syntax near '+1) AND
alarm_rep_table_id = '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, 
DATE_FORMAT(alarm_date,'%Y-%m-%d') AS adate FROM alarm_table WHERE
alarm_contact_table_id IS NULL AND adate = CURRENT_DATE AND
alarm_rep_table_id = '1' ORDER BY alarm_date;

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

Thread
How do I compare date part of datetime with CURRENT_DATE?Daevid Vincent15 Apr
  • RE: How do I compare date part of datetime with CURRENT_DATE?Daevid Vincent15 Apr
  • Re: How do I compare date part of datetime with CURRENT_DATE?Brian McCain15 Apr
RE: How do I compare date part of datetime with CURRENT_DATE?Daniel J. Conlon15 Apr