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

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