I have another way: DATEADD(d,DATEDIFF(d,0,GetDate()),0), where
GetDate() can be converted to a fieldname that you want to strip time
from. I haven't tested the speed of my method or yours, but since mine
keeps the value as a number in the middle of the conversion, and your
solution p***** it to a string, I would imagine that for large queries
this one would work better.
Hope it helps...
=Sam Curren
On Oct 16, 11:03 am, "Carol" <csch...@[EMAIL PROTECTED]
> wrote:
> Well, I found my own solution. The issue was that just using CONVERT
> did not get rid of my time. When I converted to a varchar and then
> back to a datetime, it set the time to midnight and worked - I've
> included the working query below.
>
> <CFQUERY NAME="GetEvents" DATASOURCE="#Application.DSN#">
> SELECT DepartmentName,
> EventName,
> CONVERT(datetime, CONVERT(varchar(30),
> EventStart, 101), 101) AS DateStart,
> CONVERT(datetime, EventStart, 108) AS TimeStart,
> EventStart, EventEnd, EventLocation,
> EventDescription, EventContact
> FROM IEWEvents
> GROUP BY EventStart, EventEnd, EventName, EventLocation,
> DepartmentName, EventDescription, EventContact
> </CFQUERY>
>
> Carol wrote:
> > I forgot to mention that I have already tried the following without
> > success:
>
> > <CFOUTPUT QUERY="GetEvents" GROUP="DateStart">
> > (date here)
> > <CFOUTPUT>
> > (event info here)
> > </CFOUTPUT>
> > </CFOUTPUT>
>
> > Carol wrote:
> > > I'm a bit rusty on my coding and need some guidance. I am trying to
> > > create a calendar of events and I cannot get my groupings working
> > > correctly and I'm hoping I can get some help on this issue.
>
> > > I want my output to look like so:
>
> > > MONDAY, NOVEMBER 13, 2006
>
> > > Event Department
Name
> > > Test 1 Mathematics
> > > Date: 11-13-06
> > > Time: 11:30 AM
> > > Location: Student Center
>
> > > TUESDAY, NOVEMBER 14, 2006
>
> > > Event
Department Name
> > > Test 2 Biology
> > > Date: 11-14-06
> > > Time: 10:30 AM
> > > Location: Library, 1st floor
>
> > > Robotics Technology Conference Computer Science
> > > Date: 11-14-06
> > > Time: 3:30 PM
> > > Location: Computer Science Lab 5
>
> > > and so on where events on the same day show up under that day (in
this
> > > example, Tuesday has 2 events).
>
> > > My query is:
>
> > > <CFQUERY NAME="GetEvents" DATASOURCE="#Application.DSN#">
> > > SELECT DepartmentName,
> > > EventName,
> > > CONVERT(datetime, EventStart, 101) AS
DateStart,
> > > CONVERT(datetime, EventStart, 108) AS
TimeStart,
> > > EventStart, EventEnd, EventLocation,
> > > EventDescription, EventContact
> > > FROM tblEvents
> > > </CFQUERY>
>
> > > and then for my output I have:
>
> > > <CFOUTPUT QUERY="GetEvents" GROUP="DateStart">
> > > (output here)
> > > </CFOUTPUT>
>
> > > Right now, my two Tuesday header is repeating for each of the
Tuesday
> > > events. I've tried several different solutions and haven't found
one
> > > that works correctly. I am obviously missing something. Can
someone
> > > help? Thanks so much!
|
