MySQL 5.1 implicit cast from now() to a date field

Posted by Savageman on Stack Overflow See other posts from Stack Overflow or by Savageman
Published on 2010-04-27T17:26:33Z Indexed on 2010/04/27 17:33 UTC
Read the original article Hit count: 292

Filed under:

mysql

|

implicit-cast

Hello,

I have an issue with MySQL 5.1. A datetime data type isn't implicitly casted to match a date column.

 SELECT * FROM my_table WHERE my_date_field = NOW()

This request doesn't return any rows using MySQL 5.1, but works well with version 5.0. If we use CURDATE() instead of NOW() it works both in MySQL 5.0 and MySQL 5.1. If the cast is explicit (CAST(NOW() AS DATE)), it also works both in MySQL 5.0 and MySQL 5.1.

The problem only appears with implicit cast from datetime to date. Doesn't anyone already encountered this issue or has a clue about how to solve this problem? I know it's not the best to use NOW() instead of CURTIME(), but this isn't the question here. It is currently used in an application and the purpose is to avoid rewriting everything.

Thanks!

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about implicit-cast

MySQL 5.1 implicit cast from now() to a date field

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, I have an issue with MySQL 5.1. A datetime data type isn't implicitly casted to match a date column. SELECT * FROM my_table WHERE my_date_field = NOW() This request doesn't return any rows using MySQL 5.1, but works well with version 5.0. If we use CURDATE() instead of NOW() it works both… >>> More
Identify the implicit cast and explicit cast

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello there, I would appreciate it if anyone can answer my question. Identify the implicit cast and explicit cast? int a = 2, b = 3; float f = 2.5; double d = -1.2; int int_result; float real_result; >>> More
Why does a Linq Cast<T> operation fail when I have an implicit cast defined?

as seen on Stack Overflow - Search for 'Stack Overflow'
I've created two classes, with one of them having an implicit cast between them: public class Class1 { public int Test1; } public class Class2 { public int Test2; public static implicit operator Class1(Class2 item) { return new Class1{Test1 = item.Test2}; } } When… >>> More
Implicit casting in VB.NET

as seen on Stack Overflow - Search for 'Stack Overflow'
The question is intended for lazy VB programmers. Please. In vb I can do and I won't get any errors. Example 1 Dim x As String = 5 Dim y As Integer = "5" Dim b As Boolean = "True" Example 2 Dim a As EnumType = 4 Dim v As Integer = EnumType.EnumValue Example 3 Private Sub ButtonClick(sender… >>> More
Explanation of casting/conversion int/double in C#

as seen on Stack Overflow - Search for 'Stack Overflow'
I coded some calculation stuff (I copied below a really simplifed example of what I did) like CASE2 and got bad results. Refactored the code like CASE1 and worked fine. I know there is an implicit cast in CASE 2, but not sure of the full reason. Any one could explain me what´s exactly happening below… >>> More