That is the question...given a class ID, how do you make a query that selects all other classes on that same day (but at different times)? Formatting dates for output display is not the question, but how to speci fy just the date portion of the date/time field in the query itself.
<cfquery...(stuff missing)> SELECT * FROM classes WHERE classDate = (SELECT classDate FROM classes WHERE classID = #for m.ID#) </cfquery> But this doesn't work because classDate is date and time in the SAME fiel d. This will only get me one class, the class for the sub-query ID match. I want ALL classes on the same day as the specified class mathcing form.I D. ----- Original Message ----- From: "James Curran" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, February 06, 2002 3:36 PM Subject: RE: Formatting a Date in MS SQL Access Query...Try Again > you can store the time/date data in one field and make a view/query breaking > out the separate parts into 2 new fields.. > > - j > > -----Original Message----- > From: Bruce Holm [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 06, 2002 6:32 PM > To: CF-Talk > Subject: Re: Formatting a Date in MS SQL Access Query...Try Again > > > Thanks for the response and tips. > > Are you suggesting I use a date and a time column in my classes db? > With several classes per day, they each have a time of day. I was tryi ng to > be efficient and use a single date/time field to store this info. The trick > then becomes how to find records with the same date (ignoring the time > element) for all classes on any given day. > > Bruce > > ----- Original Message ----- > From: "Bud" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Wednesday, February 06, 2002 3:08 PM > Subject: Re: Formatting a Date in MS SQL Access Query...Try Again > > > > On 2/6/02, Bruce Holm penned: > > >BTW, when I created the date in Access, I specified the Date format to be > > >General which stores date and time info in the SAME field. > > > > BTW Bruce. Access always stores dates as date and time. So does SQL > > Server. When you choose type of date/time when creating the field, > > you're just telling Access how to display it when looking at the > > table directly. > > > > The trick is, when you insert a date with a SQL query, insert it with > > createodbcdate. Then the time portion of any date/time you enter, > > such as #createodbcdate(now())#, will be inserted as 00:00:00. Then > > the search using# createodbcdate(mydate)# will return that date. This > > way you won't need to search for a range if looking for a single date. > > -- > > > > Bud Schneehagen - Tropical Web Creations > > > > > ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb 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
