Compare date to months and day only

@r937

I have table userregister

CREATE TABLE `userregister` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
 `lastname` varchar(50) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `dateregister` date NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1

if this is the

 $currentdate = "2014-09-30";

How can i make select statement to compare my $currentdate to dateregister having the same month and day only.

Thank you in advance.

You’ll need to use MONTH and DAY in mySQL and date_format for the PHP variable.

@DaveMaxwell

Thank you for the reply,…I could not figure it out the query…I am confuse month and day.

Thank you in advance.

sigh - Something like this should work (a couple quick google searches would have given you the syntax since I gave you the terms)

$query = "SELECT lastname, firstname
            FROM userregister
           WHERE MONTH(dateregister) = " . date_format($currentdate, "m") .
             AND DAY(dateregister) = " . date_format($currentdate, "d")

dave, you’re comparing numerics and strings

here’s another approach, which fully incorporates the basic requirement and at the same time makes use of an index (which should exist) on the column…

WHERE dateregister >= CURRENT_DATE AND dateregister < CURRENT_DATE + INTERVAL 1 DAYworks regardless of whether dateregister is DATE or DATETIME dataype

I’m confused. How does that get what he wants? Yours returns where the dateregister falls onto today. I read the OP as the user is looking almost for a trend query. So, if the user has 30 Sept., show all registrations for that date regardless of year (so 30 Sept. '14, 30 Sept. '12, etc.)

If I misinterpreted, I apologize…

yeah, good point, dave

@r937 @DaveMaxwell,

Thank you so much,I apologize for the late reply.

so which of us had the right interpretation?

1 Like