Here is a similar bit of code that I used to get records between certain
dates.
<!--- Set Start and End Dates for the appointments --->
<cfset ODBCStartDate = "#DateFormat(Request.Week.Monday,
'YYYY-MM-DD')#" & " 07:00:00">
<cfset ODBCStartDate = CreateODBCDateTime(ODBCStartDate)>
<cfset ODBCEndDate = "#DateFormat(Request.Week.Friday,
'YYYY-MM-DD')#" & " 18:00:00">
<cfset ODBCEndDate = CreateODBCDateTime(ODBCEndDate)>
<cfloop query="qDisplayResources">
<CFQUERY NAME="qGetBookingAppoinments"
DATASOURCE="#Request.DSN#">
SELECT dbo.AppointmentType.ShortDescription,
dbo.AppointmentType.AppointmentTypeID,
dbo.AppointmentType.Background, dbo.AppointmentType.Foreground,
dbo.Appointments.Client, dbo.Appointments.Date,
dbo.Appointments.EmployeeID, dbo.Appointments.AppointmentID
FROM dbo.Appointments, dbo.AppointmentType
WHERE dbo.Appointments.EmployeeID = #EmployeeID# AND
dbo.Appointments.AppointmentTypeID =
dbo.AppointmentType.AppointmentTypeID AND
(dbo.Appointments.Date >= #ODBCStartDate#) AND
(dbo.Appointments.Date <=
#ODBCEndDate#)
ORDER BY DATE
</CFQUERY>
You will notice that I added my own start time and end time. I have noticed
some issues with the datepart function and thus did a work around with the
above code.
Sincerely,
Matthew M. Eschenbaum, ACP
Systems Developer
[EMAIL PROTECTED]
ph: (206)956.0888 ext.24
fax:(206)956.4460
_______________________
DevTech, Inc.: Web Application Training & Development
www.dev-tech.com
600 Stewart Street
15th Floor
Seattle, WA 98101
-----Original Message-----
From: Eric Dawson [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 16, 2001 2:00 PM
To: CF-Talk
Subject: RE: (Easy SQL)
hmmm. I am getting a "'dateformat' not a recognized function" error. So I
need the SQL statement similar to
select datepart(yyyy,signup_datetime) as year, datepart(mm,signup_datetime)
as month, datepart(d,signup_datetime) as day, count(userID) as NewMembers
from membertable
group by datepart(yyyy,signup_datetime), datepart(mm,signup_datetime),
datepart(d,signup_datetime)
I just can't believe this is the way you have to do it.
Also: how would I reconnect date(year,month,day) as datetime field in the
returned query results.
Eric
Note I am trying to limit the amount of record that are returned by using
SQL aggregate functions. It seems like there should be a way to easily group
by justthedatepartofadatetimefield but I can't find any syntax to do it.
Eric
From: "John Wilker" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: CF-Talk <[EMAIL PROTECTED]>
Subject: RE: (Easy SQL)
Date: Mon, 16 Apr 2001 13:25:32 -0700
dateformat(signup_datetime, "mm/dd/yyyy")
Or use any mask you choose for the mm/dd/yyyy part. Just run your query and
grab the fields, then do the mask in CF when displaying the data.
J.
John Wilker
Web Applications Consultant
Allaire Certified ColdFusion Developer
www.red-omega.com <http://www.red-omega.com>
Pepsi's "Come Alive With the Pepsi Generation" translated into "Pepsi Brings
Your Ancestors Back From the Grave" in Chinese.
-----Original Message-----
From: Eric Dawson [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 16, 2001 12:09 PM
To: CF-Talk
Subject: (Easy SQL)
I have a member table which contains
userid, signup_datetime
I want to create a daily summary of registrations.
select signup_datetime as justDatenotTime,count(userID) as NewMembers
from membertable
group by justDatenotTime
== * ==
I know this has to be simple, but I can't find the syntax. How do I drop the
time from a datetime field (SQL Server 7/2000/MSDE) so I can group by just
the day?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
