List:General Discussion« Previous MessageNext Message »
From:Daniel J. Conlon Date:April 15 2003 9:37pm
Subject:RE: How do I compare date part of datetime with CURRENT_DATE?
View as plain text  
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

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