• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1476
  • Last Modified:

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.

0
pmessana
Asked:
pmessana
  • 3
  • 2
1 Solution
 
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
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
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:
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now