...

View Full Version : query to get all records between 2 different field dates



babelfish
03-03-2009, 04:58 PM
sorry if the question was confusing lol...

anyway, this is what i need to do but it doesnt work:


SELECT id, user_worked, approved_by FROM timesheets WHERE timesheet_type='Holiday' AND date_worked >= '2009-03-5' AND date_end <= '2009-03-5' ORDER BY user_worked


syntax is dodgy, obviously. :confused:

its for a calendar im working on. the problem appears to be that im using 2 date fields (end_date and date_worked [which is the start date really])

any ideas? i know i could use BETWEEN if there was only 1 date field. but having 2 date fields is confusing me (but needed for the way this application works)

thanks in advance all!

Fumigator
03-03-2009, 05:40 PM
You should be able to add WHERE date_end >= date_worked AND date_worked <= date_end to close the loose ends of the date range.

babelfish
03-03-2009, 05:48 PM
? those are already always right, i check them before inserting into mysql.

maybe i didnt explain myself very well (v tired today zzzzz)

basically all holidays have a start date and an end date (end date must be same or after start date [for people booking hols for 1/2 or single days])

i am then going through a calendar checking if there are any hols for each date. (this is basically a copy/paste from an older system that just created separate records for each date - this was a doddle to get working for that one lol)

so, for example the date today is 2009-03-03 so i need to get records if this date falls between start_date and end_date.

does that make any more sense now? need caffeine!

Fumigator
03-03-2009, 06:02 PM
No I'm the one thinking with my foot today...

I guess I would agree with you, your query should work! So... can you show an example of a result you got that you didn't expect to get? Maybe that will help me think.

Old Pedant
03-04-2009, 04:08 AM
No, I don't see how that works.

SELECT id, user_worked, approved_by
FROM timesheets
WHERE timesheet_type='Holiday'
AND date_worked >= '2009-03-5' AND date_end <= '2009-03-5'
ORDER BY user_worked

Let's say the user took a holiday starting 2009-03-01 and ending 2009-03-31

If I read you correctly, that means you will have a record that looks like:


timesheet_type :: date_worked :: date_end
Holiday :: 2009-03-01 :: 2009-03-31

And of course your WHERE clause finds no match on that.

Right?

I think you simply have your comparisons backward. Try


AND date_worked <= CurDate() AND CurDate <= date_end

And note that this means you *CAN* do


AND CurDate BETWEEN date_worked AND date_end


No???

[Doesn't have to be CurDate, of course...just use whatever date you want to test for.]

Old Pedant
03-04-2009, 04:14 AM
i am then going through a calendar checking if there are any hols for each date.
Please don't tell me you are checking each date individually, in a separate SQL query???



SELECT DT.theDate, TS.id, TS.user_worked, TS.approved_by
FROM tableOfDate AS DT LEFT JOIN timesheets AS TS
ON ( TS.timesheet_type='Holiday'
AND DT.theDate BETWEEN TS.date_worked AND TS.date_end )
ORDER BY DT.theDate, TS.user_worked

Or something along those lines. Now you'll get all dates, all holidays taken. (The ORDER BY can of course be in the other order, but you will have some records where TS.user_worked is NULL if nobody took a holiday on that date, of course.)

Hmmm???

babelfish
03-04-2009, 09:37 AM
[QUOTE=Old Pedant;788782]No, I don't see how that works.


AND CurDate BETWEEN date_worked AND date_end

QUOTE]

that worked a treat mate



$query = "SELECT id, user_worked, approved_by FROM timesheets WHERE timesheet_type='Holiday' AND date_worked <= '$year-$month-$day_num' AND '$year-$month-$day_num' <= date_end ORDER BY user_worked";


the reason im looking up for each date seperately is that im making a dynamic calendar - so each date is created as a table cell and then we query for any holidays on that day. im not bothered about performance as this is a page that wont be used much (mainly by management) and its only a small project management system.

thanks for the help though! :) im still pretty new to sql and php - this is my first app!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum