WHERE day, month, year from datetime column

[lukep11a]

Does anybody know how I can select the day, month and year from the variable $date which is stored as datetime. I am trying to display all fixtures for each day that there are fixtures for. I am doing it like this as some fixtures have the same date but different kick off times.

 

So, I was thinking something like WHERE Month(fixtures.date) = '$date' AND Day(fixtures.date) = '$date' but it's not returning any results. Here is my code:

 

$query1="SELECT home.team_name AS home_team, away.team_name AS away_team, competitions.comp_name
			FROM fixtures
			LEFT JOIN teams home
			ON fixtures.ht_id = home.team_id
			LEFT JOIN teams away
			ON fixtures.at_id = away.team_id
			LEFT JOIN competitions
			ON fixtures.comp_id = competitions.comp_id
			WHERE Month(fixtures.date) = '$date' AND Day(fixtures.date) = '$date'
			GROUP BY fixtures.fixture_id
			ORDER BY fixtures.date ASC";

[Pikachu2000]

How can $date = both MONTH(fixtures.date) and DAY(fixtures.date) at the same time?

[lukep11a]

$date is selected from fixtures.date column and could contain

 

2012-07-27 15:00:00

 

so i want to select all fixtures that are on 27 July, ie:

 

2012-07-27 15:00:00

2012-07-27 18:00:00

2012-07-27 21:00:00

 

and not just the ones that are exactly equal to 2012-07-27 15:00:00 as some fixtures have different kick off times, if that makes sense?

[Barand]

$date = '2012-07-28';

 

... WHERE DATE(fixtures.date) = '$date'

[lukep11a]

Thanks Barand, $date is defined by $row['date'] which is from the fixtures table where the row is set as datetime format, it is set in this format so that it includes the date of the fixture and the kick off time. As not all fixtures kick off at the same time I need to find a way to show all fixtures from any day that there are games scheduled.

[Pikachu2000]

Look at your comparisons. You're trying to compare the $date variable in YYYY-MM-DD hh:mm:ss format against MONTH(fixtures.date) and also against DAY(fixtures.date). Next, look at the values returned by those two MySQL functions, and you'll see why using the variable $date will not work.

 

DAY()

MONTH()

[lukep11a]

Do you know a way that I can make it work?

 

I've converted $date to $date = date('Y-m-d', strtotime($row['date']));

 

then I though something like this in the WHERE statement to convert fixtures.date into yyyy-mm-dd, WHERE CONVERT(CHAR(10),fixtures.date,120) = '$date' but can't seem to get it working

[Barand]

Thanks Barand, $date is defined by $row['date'] which is from the fixtures table where the row is set as datetime format, it is set in this format so that it includes the date of the fixture and the kick off time. As not all fixtures kick off at the same time I need to find a way to show all fixtures from any day that there are games scheduled.

 

DATE(fixture.date) will return the date only part of of the datetime field and ignore the time portion. This enables you to compare with another date, such as I gave you in my earlier example.

[Barand]

WHERE CONVERT(CHAR(10),fixtures.date,120)

 

Are you one of those poor devils having to cope with MS SQL SERVER's pathetic armoury of date/time functions?

[lukep11a]

I'm really sorry Barand. I didn't read your first reply properly. DATE(fixtures.date) was exactly what I was looking for, it's now working. I didn't expect it to be that simple especially after reading up on SQL date/time functions. There are so many I didn't really know where to start!!

 

Thanks for your help... again!