TRUNC( date ) sets a date to be 12 AM of the date.
So when comparing two dates, it makes sense to have
the dates be the same time, unless of course
you are concerned about the time of day.
TRUNC() lets you compare apples to apples.
-Rice
-----Original Message-----
From: Dina Hess [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 10:41 AM
To: CF-Talk
Subject: Truncate?
Uh...would someone mind briefing me on the TRUNC or TRUNCATE function? Can't
find either in my SQL or CF references.
----- Original Message -----
From: Rice, John J
To: CF-Talk
Sent: Thursday, August 30, 2001 8:35 AM
Subject: RE: Systdate Query
This should work:
/**/
WHERE userid='#client.cim_userid#'
AND
TRUNC(dateTaken) >= TRUNC(TO_DATE('29-8-2001','dd-mm-yyyy'))
AND TRUNC(dateTaken) <= TRUNC(TO_DATE('29-8-2001','dd-mm-yyyy'))
/**/
making the date variable of course...
I know there is a better way, but off the top of the noggin this
should work.
cheers
-Rice
-----Original Message-----
From: Rayna Evans [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 10:22 AM
To: CF-Talk
Subject: RE: Systdate Query
Thanks Dave, but it's still not quite there. Here is the scenario...maybe
this will make it more clear.
I have developed an application that involves a bunch of tests...when the
user completes one of the tests, their results will be displayed on a
profile page, there is a hyperlink on the title of each test on the
profile
page which will take the user to a profile summary page. The profile page
just gives the user the results of the test taken on the most recent date
(if they took a test twice on the same day it should display all their
results on the profile page). If they chose the hyperlinked title on the
profile page, they will go into the summary page and see all the results
of
their previous test results on the summary page (which gives them a view
of
what results they got during other dates they took the test). Hope that
makes sense.
Now, I had it working right the other day...but since I am a newbie to
Cold
fusion, I don't know how I did it and I Have tried every variation with my
SQL statement...it's like I am almost there, but not quite. So, if I
don't
use the datetaken subquery in my statement...EVERYTHING displays on the
profile page and i just need all the results from the most recent
date...all
the other results from the other dates should display on the summary page.
I am about to jump out the window on this one.... :-(
Rayna
Rayna Evans
AAMC
2501 M Street, 2nd Fl
Washington, DC 20037
202-862-6243 (direct)
Extension 4243 (internal)
>>> [EMAIL PROTECTED] 08/30/01 07:58AM >>>
Oops. Misread your message last time...
Instead, try this:
and datetaken between '01/01/2001 00:00:00' AND '01/01/2001 23:59:59'
(substitute date where appropriate) - this will find everything for the
first of this year. i don't know of a way to do this in your subquery,
however... you'll have to know the date you're searching on ahead of time
rather than doing a max(..) in the same query.
-----Original Message-----
From: Rayna Evans [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 9:41 AM
To: CF-Talk
Subject: Systdate Query
Hi all,
I am trying to pull up all data entered for a particular day (yesterday)
in
my output page. Here is the query:
<cfquery name="getEducation" datasource="#variables.cimdata#">
SELECT classenj, course, why, datetaken
FROM cim_educationalexp
WHERE userid='#client.cim_userid#' AND datetaken = (select
MAX(datetaken)
from cim_educationalexp where userid='#client.cim_userid#')
</cfquery>
My question: is there anyway to pull all the data entered for that date?
In
the subquery (select MAX(datetaken) from cim_educationalexp where
userid='#client.cim_userid#'), I have MAX for the datetaken variable (or
sysdate), but what i am trying to do is get all the data entered for that
day. what i am getting is the last data entered for that day. There are
4
other enteries that are from that day and I can't get them to come up.
Any suggestions?
Thanks,
Rayna Evans
Rayna Evans
AAMC
2501 M Street, 2nd Fl
Washington, DC 20037
202-862-6243 (direct)
Extension 4243 (internal)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
