SQL function, eh? I'll try it out.
  ----- Original Message ----- 
  From: Rice, John J 
  To: CF-Talk 
  Sent: Thursday, August 30, 2001 9:54 AM
  Subject: RE: Truncate?



  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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Reply via email to