where date = today

[hassank1]

I want to select from a datetime field where the date = today .. (I don't care what time of the day) .. how can I do that ?

[Mchl]

WHERE dateTimeField = DATE(NOW())

[fenway]

You can actually just use NOW()... no need for wrapping in DATE().

[hassank1]

I guess Now() will take the time into consideration

[Mchl]

Yes... and I screwed up

 

WHERE DATE(dateTimeField) = DATE(NOW())

[fenway]

Yes... and I screwed up

 

WHERE DATE(dateTimeField) = DATE(NOW())

In fact, mysql is quite lax, and will happily match up date with date/time fields even without DATE() wrapping.

[Mchl]

I disagree.

 

DROP TABLE IF EXISTS `dateTable`;
CREATE TABLE  `dateTable` (
  `d` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `dateTable` VALUES (NOW());

SELECT d FROM `dateTable` WHERE d = DATE(NOW());

-- 0 rows returned

SELECT d FROM `dateTable` WHERE DATE(d) = DATE(NOW());

-- 1 row returned

[fenway]

Sorry, I missed what was going on here... I thought you were comparing a DATE field to NOW().  FYI, it's easier to use:

 

SELECT d FROM `dateTable` WHERE d = CURDATE();

[Mchl]

Heh... yeah, it probably is Forgot abut this function.

[fenway]

Heh... yeah, it probably is Forgot abut this function.

Actually, I use it to be explicit -- CURDATE() when I'm just comparing the date, vs. NOW() when I mean the full date & time.  Just a convention of mine, but it helps.