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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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( )
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.