Solved

Selecting records according to date

Posted on 2007-03-22
6
279 Views
Last Modified: 2013-12-13
Hi there,

I'm having trouble selecting events from my database appropriate to a specific month and year of the user's choice.

Suppose for the following examples, the user wishes to view all records where the event is during, or overlaps into, April 2007.

The following query produces results:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE (tblevents.event_from BETWEEN '2007/04/01' AND '2007/04/30') OR (tblevents.event_to BETWEEN '2007/04/01' AND '2007/04/30') ORDER BY event_from ASC

However, this version does not:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE ('2007/04/01' BETWEEN tblevents.event_from AND tblevents.event_to) OR ('2007/04/30' BETWEEN tblevents.event_from AND tblevents.event_to) ORDER BY event_from ASC


The first version works largely, but if an event spans from say January - June, it won't return such rows because the to and from date column values fall outside the current month. I figured I could fix this using the 2nd query shown about, but that doesn't seem to want to return any rows at all. I guess the BETWEEN syntax doesn't take date columns as the arguments in that way? Why, I can't understand though.

Can someone please tell me how to fix this? I know I could adjust my system to record an event_id against a date in a new table, but I doubt there's any need for that and some nice SQL should yield the results I require...can it?

Hope you can help!

Many thanks.
0
Comment
Question by:m175400
6 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 18771882
This one below should work unless your event can start before April and end after April.
If so, you need to do a cross join to a calendar containing an entry for every day.

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires,
DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to,
DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from
FROM tblevents
WHERE (year(tblevents.event_from) = 2007 or year(tblevents.event_to) = 2007)
AND (month(tblevents.event_from) = 4 or year(tblevents.event_to) = 4)
ORDER BY event_from ASC


Cross join to a calendar containing an entry for every day - Example

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires,
DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to,
DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from
FROM tblevents cross join (select caldate from mycalendar where mycalendar.caldate BETWEEN '2007/04/01' AND '2007/04/30') mycalendar
WHERE mycalendar.caldate between tblevents.event_from and tblevents.event_to
0
 
LVL 1

Author Comment

by:m175400
ID: 18771938
Sorry, so how does mycalendar table work?

Trying to visualise this. Do I just need one column with every date day by day for like 30yrs over which the site could be used? Or do I need an entry for each day the event is on e.g.

tblcalendar
calendar_id
calendar_date
event_id

?
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 125 total points
ID: 18772236
You can use a table as you suggested.
The following is an example of how to return all the days from the current date to the end of the month (in MS SQL SERVER though -you will need to change it to MySql)


Use it instead of the [(select caldate from mycalendar where mycalendar.caldate BETWEEN '2007/04/01' AND '2007/04/30') mycalendar] part

SELECT dateadd(dd,0, datediff(dd,0,DATEADD(dd, seq.id, getdate()))) calDate
FROM (SELECT (a1.id + a0.id) id
        FROM (SELECT 0 id UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                  SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
        ) a0      CROSS JOIN (SELECT 0 id UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30
        ) a1) seq
WHERE month(DATEADD(dd, seq.id, getdate())) = month(getdate())
0
 
LVL 2

Assisted Solution

by:ckyteh
ckyteh earned 125 total points
ID: 19055552
Hi,

If your query is generally working except in the case where the event spans the entire month, then perhaps add another condition to check for that. This can be done by checking if the "from" date is before the start of the month AND the "to" date is after the end of the month. For example, add:

OR (tblevents.event_from < '2007/04/01' AND tblevents.event_to > '2007/04/30')

So you would end up with:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE (tblevents.event_from BETWEEN '2007/04/01' AND '2007/04/30') OR (tblevents.event_to BETWEEN '2007/04/01' AND '2007/04/30') OR (tblevents.event_from < '2007/04/01' AND tblevents.event_to > '2007/04/30')  ORDER BY event_from ASC
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21207277
Forced accept.

Computer101
EE Admin
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question