Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

selecting dates ragardless of year 1

Status
Not open for further replies.

bezierstek

Programmer
Aug 30, 2007
43
FR
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.

Code:
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,
 
That doesn't seem to work with the variable $currentdate. In php I am writing teh statement as follows.

Code:
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?
 
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
Code:
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
 
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
 
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.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top