Is:
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 TO_DAYS(alarm_date) =
TO_DAYS(CURDATE()) AND alarm_rep_table_id = '1'
ORDER BY alarm_date;
Any good?
> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: 15 April 2003 22:28
> 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=1
