Compare of CURDATE and DATE

I am running the following query in MySQL 5.0.45.  

It would appear as though CURDATE and DATE do not compare to each other properly.

Here is my query: SELECT count(*) as shipped FROM orders WHERE date( ship_dt ) = curdate( )

The issue is that it is not comparing the two dates at all and ignoring it altogether.  If I run SELECT date(ship_dt), currdate FROM orders

The dates look correct but they will not compare in the where clause.

ship_dt is set to DATETIME.

This is probably something smple...running 5.0.45.

LVL 9
pmessanaAsked:
Who is Participating?
 
Roger BaklundCommented:
Strange. I have a 5.0.45 server here (5.0.45-community-nt, running on MS Vista). Running the same query on one if my tables with a DATETIME column gives the expected results (17 clicks for today is correct):
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.00 sec)
 
mysql> desc stats;
+-----------+------------------+------+-----+---------------------+-------+
| Field     | Type             | Null | Key | Default             | Extra |
+-----------+------------------+------+-----+---------------------+-------+
| clicktime | datetime         | NO   | MUL | 0000-00-00 00:00:00 |       |
| c         | varchar(255)     | NO   |     |                     |       |
| id        | int(11)          | YES  |     | NULL                |       |
| cmd       | varchar(32)      | YES  |     | NULL                |       |
| userid    | int(11)          | NO   |     | 0                   |       |
| ip        | int(10) unsigned | YES  |     | NULL                |       |
| ua        | int(11)          | YES  |     | NULL                |       |
| seq       | int(11)          | YES  |     | NULL                |       |
+-----------+------------------+------+-----+---------------------+-------+
8 rows in set (0.01 sec)
 
mysql> select count(*) as clicks from stats where date(clicktime)=curdate();
+--------+
| clicks |
+--------+
|     17 |
+--------+
1 row in set (0.00 sec)

Open in new window

0
 
pmessanaAuthor Commented:
Sorry, those links do not help to explain why I can run a select and get results that look the same but when used in the WHERE clause it does not compare them.

For a bit more details, if I run the following query:
SELECT date(ship_dt), currdate() FROM orders

I get:  2008-11-18 for date(ship_dt) and I get 2008-11-18 for currdate()

So this leaves me to wonder why I would not be able to compare date(ship_dt) to currdate()

They appear formatted the same, etc.  I guess I could easily rectify this in my code to pass the date field in as a string, but I would prefer to accomplish this in the query.

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Roger BaklundCommented:
currdate() does not work at all on my server (5.0.67), I have to write curdate().

I get a 1 from the query below, do you? If so, the type returned from date() and from curdate() should be compatible. If you get 0, your server probably thinks these are of different type, and you could try casting to a common type.
select curdate()=date(now());

Open in new window

0
 
pmessanaAuthor Commented:
Sorry, I mistyped that, I am using CURDATE, not CURRDATE.

I get a 1 when I run your select query so I am still confused as to the problem.  My ship_dt field is a datetime and when run in the select they are both looking the same.

However, to add to the interesting nature of this, I was able to write the following query and it works, since it is returning in the SELECT side just fine I am using the HAVING to compare, which works, still don't understand the issue with the WHERE clause other than this is a bug in MYSQL.

SELECT date( ship_dt ) AS sdate, count( * ) AS shipped
FROM orders
WHERE allocate =6
AND orderstatus =2
GROUP BY sdate
HAVING sdate = curdate( )
0
 
pmessanaAuthor Commented:
Very bizarre, I cannot get it to work on my install but still awarding the points based on the fact that this is accurate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.