I will dig into your suggestion (by default) it returns 0 records no matter what the form dates are.
I truely appreciate your help! Paul Giesenhagen QuillDesign ----- Original Message ----- From: "Matthew Walker" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, September 16, 2002 11:28 PM Subject: RE: OT SQL query (dates) > How about... > > > SELECT sd.id > FROM settingTable AS sd, > settingTable AS ed > WHERE (sd.datatype="startdate") > AND (ed.datatype="enddate") > AND ( > (sd.datedata Between #form.startdate# > And #form.enddate#) > OR (ed.datedata Between #form.startdate# > And #form.enddate#) > OR ( > (#form.startdate# between > sd.datedata and ed.datedata) > and (#form.enddate# between > sd.datedata and ed.datedata) > ) > ) > > > -----Original Message----- > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, 17 September 2002 4:05 p.m. > > To: CF-Talk > > Subject: Re: OT SQL query (dates) > > > > > > There is only one startDate and one endDate ... > > > > So alittle more of an example: > > > > id datatype smalltext longtext datedata > > =============================== > > 1 dtValue my value > > 2 dtValue2 Looong > > 3 startDate 10/01/2002 > > 4 endDate 10/31/2002 > > 5 dtValue3 Another > > ect... > > > > startDate and endDate datatypes don't have to correlate with > > the other rows > > (but they can). > > > > This table setup allows the use of generic data ... we use it > > for settings > > . since there are so many different types of settings, it is > > easy to write > > a query like this to get a value > > > > SELECT smalltext > > FROM settingsTable > > WHERE datatype = 'dtValue' > > > > <cfoutput>#smalltext#</cfoutput> = "my value" > > > > So our query is is looking at the startDate and the endDate > > datatypes for > > the datedata values. > > > > make sense? > > > > Paul Giesenhagen > > QuillDesign > > > > ----- Original Message ----- > > From: "Matthew Walker" <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Monday, September 16, 2002 11:01 PM > > Subject: RE: OT SQL query (dates) > > > > > > > Are there only two rows inn your table? If not, how do you correlate > > > this startdate with that enddate? > > > > > > > -----Original Message----- > > > > From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]] > > > > Sent: Tuesday, 17 September 2002 2:30 p.m. > > > > To: CF-Talk > > > > Subject: OT SQL query (dates) > > > > > > > > > > > > I have a bit of a challenge ... may be easy for someone, but > > > > I am missing the thought process here. > > > > > > > > I have a startdate and and enddate in the database.. I have a > > > > form that is submitting another start and end date. I need > > > > to write a query to check the start and end dates in the > > > > database, compare and make sure that the new start and end > > > > dates DO NOT OVERLAP... (plus the table is setup alittle oddly). > > > > > > > > So if I have in the db: > > > > > > > > startdate = 10/01/2002 > > > > enddate = 10/31/2002 > > > > > > > > and submitting form > > > > > > > > form.startdate = 10/15/2002 > > > > form.enddate = 10/20/2002 > > > > > > > > I want to be able to check and make set the recordcount to 1 > > > > (or a number other than 0) ...thus I will know that this is > > > > invalid because there is already a date range specified. if > > > > the form.startdate and form.enddate is after (or before) the > > > > DB's start and end dates, then give me a 0 recordcount and I > > > > know that it is valid. > > > > > > > > Below is the table setup and the data within it ... > > > > > > > > table is settingsTable > > > > id datatype datedata > > > > =========================== > > > > 1 startDate 10/01/2002 > > > > 2 endDate 10/31/2002 > > > > > > > > I tried this query > > > > > > > > SELECT sd.id > > > > FROM settingTable sd, settingTable ed > > > > WHERE sd.datedata <= #CreateODBCDate(form.promoStartDate)# > > > > AND ed.datedata >= #CreateODBCDate(form.promoEndDate)# > > > > AND sd.datatype = 'promoStartDate' > > > > AND ed.datatype = 'promoEndDate' > > > > > > > > But it always returns a 0 > > > > > > > > Any help would be greatly appreciated > > > > > > > > Paul Giesenhagen > > > > QuillDesign > > > > > > > > > > > > > > > > > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
