selecting dates ragardless of year 1

[bezierstek]

Hi,

I have a table that holds dates in it for an availability calendar script. At the moment I hold the season dates in a date field and colour code the calendar to show the rental season. this is fine for the current year but if I want to display the next year (which has exactly the same rental seasons), I don't get anything coloured because the script is comparing the years along with the rest of teh date.

What I want to be able to do is for example if a rental period is from the 1st of July to the 31st of August to be able to pull out the dates from the database where just those months are included. I store both a start date and an endate for each season in the same record.

I suppose what I am really after is a way to structure a select statement something like this.

select * from pricing where day and month of startdate <= day and month of currentdate and day and month of day and month of endate >= currentdate

if that makes any sense. I know the statement above is not anywhere like a correct select statement but it just seems the easiest way to describe it.

Can anyone help?

Thanks,

 

[hoinz]

Have a look at extract() function,

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_extract

So something like

select * from pricing where extract(month_day from startdate) <= extract(month_day from currentdate) and ...

hope this helps

 

[bezierstek]

That doesn't seem to work with the variable $currentdate. In php I am writing teh statement as follows.

select * from pricing where extract(month_day from startdate) <= extract(month_day from '$currentdate') and extract(month_day from enddate) >= extract(month_day from '$currentdate')

With this I am getting an error which I believe is due to teh php variable in there. Is there any way round this?

 

[hoinz]

I don't know how to do it in PHP;
But what about Mysql's curdate() function?

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_curdate

 

[bezierstek]

actually I have worked it out why your code didn't work. there is no month_day in mysql (at least not in the version I'm using). Is there any other way I could emulate this.

I do have some other mysql

select * from pricing WHERE (day(startdate)<=day('$currentdate') AND month(startdate)<=month('$currentdate')) AND (day(enddate)>=day('$currentdate') AND month(enddate)>=month('$currentdate'))

But this gives the problem that one rental period start on the 7th of september and goes on to the end of the year. Whit eh above code the 1st seven days of each month (Sept to December) get missed out.

Richard

 

[hoinz]

Then it will have to become a bit more complicated, something like:

... WHERE (month(startdate)<month('$currentdate') OR (month(startdate)=month('$currentdate') AND day(startdate)<=day('$currentdate')))
AND ...similar for enddate

 

[bezierstek]

Thanks for the help.

I have managed to get it working by using a bit of a hack (using order desc and changing the period that started on the 8th so it overlapped the previous one). Not perfect certainly but it is working for teh moment. I'll have a look at your code though as it would be preferable to have it working properly.