Following up, it seems I was correct in that CURRENT_DATE has a 12:00:00 AM
time associated with it as well. Grrr.:
mysql> select DATE_FORMAT(CURRENT_DATE,'%m/%d/%y %h:%i %p');
+-----------------------------------------------+
| DATE_FORMAT(CURRENT_DATE,'%m/%d/%y %h:%i %p') |
+-----------------------------------------------+
| 04/15/03 12:00 AM |
+-----------------------------------------------+
So was able to do this:
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
<= DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) 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 |
+-----------------+---------------------+-------------------+
However this seems klunky and not the best way to do it. I'd still like to
hear if there is a more efficient or cleaner way.
*sigh*
> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: Tuesday, April 15, 2003 2:28 PM
> To: mysql@stripped
> Subject: How do I compare date part of datetime with CURRENT_DATE?
>
>
> 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'
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> unsub=daevid@stripped
>
>
