We help IT Professionals succeed at work.

Query to get events in a week

jastroem
jastroem asked
on
How can I write a query to draw records from a DB where the field "StartDate" is in a specific week

<cfquery name="getWeekRows" datasource="ksw">
SELECT * FROM Events
Where StartDate = ?
</cfquery>

Missing is the expression [?] to get the events where event startdate falls within a week 1 - 52.

Hope someone can help me!
Kind regards
Joergen Astroem
Comment
Watch Question

Commented:
Just pass a Start and End Date to the query:

<cfquery name="getWeekRows" datasource="ksw">
SELECT * FROM Events
Where StartDate BETWEEN #StartDate# AND #EndDate#
</cfquery>

Now if you need a function to figure out the Start and End Dates, given, say, the Current Date, let me know, and I can get something together for you.

:) dapperry

Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
SILVER EXPERT

Commented:
<learning..>
Commented:
Do you want your week to be from Sunday to Saturday (or whatever your SQL server has as its start of week) or seven days from the value passed in StartDate?

First, the Sunday to Saturday scenario. Unless you are going to build the logic into your form to restrict the user from selecting/entering a date that is not Sunday, a StartDate/EndDate scenario will be difficult.  So, because you are not going to limit the StartDate the user can select/enter, use CF's WEEK() function with the DatePart() function.

<cfquery name="getWeeks" datasource="ksw">
  SELECT *
  FROM Events
  WHERE DatePart(wk,event_date) = #week(StartDate)#
</cfquery>

Lastly, the seven day scenario. The user will select/enter the StartDate. You then add/subtract seven days to get your EndDate.

<cfset Variables.EndDate = DateAdd("d", 7, StartDate)>

Then use the query from 'dapperry' and you'll be on your way.

Author

Commented:
Thanks a lot!!

I'll have a look at this on friday, and then I will return to you.

Regards
Joergen

Commented:
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:

Split dapperry & reitzen

Please leave any comments here within the next four days.

mrichmon
EE Cleanup Volunteer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.