[RESOLVED] Advanced only: truncate datetime field for comparison

ipruthi

Hello everyone,

I'm working on a news archive and I'm given the $year and the $month. I have to construct an SQL statement that will query the database and return the days in the given month that have articles posted.

This is the database setup:

[CODE]

id | title | content | date |

1 | So... | Today... | 2006-06-28 12:34:14 |
...
[/CODE]

This is the query I wrote:

SELECT DISTINCT(`date`)
	FROM	`$tableposts`
	WHERE	LEFT(`date`,10) >= '$dateBeg' AND
		LEFT(`date`,10) <= '$dateEnd'
	ORDER BY `date` ASC
	LIMIT 32

[SIZE=2]where $dateBeg is first day of month and $dateEnd is last day of month[/SIZE]

What I want to do is:
select distinct days where the truncated datetime field falls within the month's range

I tried the LEFT function but it doesn't work. I want the datetime field to be excluded of the time portion; basically be treated as a DATE field by this query.

Is there a way to achieve this? The next thing after this would be to retrieve all rows from DB and then sort out the duplicates via PHP, though that would be a daunting task though :bemused:

Thanks for your help.

Sxooter

Perhaps you can use between?

select * from yourdb where datefield between '2006-01-01' and '2006-02-01';

liquorvicar

date_format

would something like

SELECT date_format(`date`,'%Y-%m-%d') as date_no_time 
	FROM	`$tableposts`
	WHERE	date_no_time between '$dateBeg' AND  '$dateEnd'
        GROUP BY date_no_time 
	ORDER BY date_no_time  ASC

work for you?

ipruthi

Thank you for your responses, guys.

I figured it out: I was using LEFT() in the wrong place. I moved it up to within the DISTINCT() function and created a nested field. I also used the BETWEEN operator as suggested.

My SQL query now looks like this (and works as expected):

SELECT DISTINCT(LEFT(`date`,10))
	FROM 	`$tableposts`
	WHERE 	`date` BETWEEN '$dateBeg' AND '$dateEnd'
	ORDER BY `date` ASC
	LIMIT 32

Again, thank you for your input. The PHPBuilder forum is an invaluable resource when you need help from pros.