Skip to main content
A
Anonymous
January 27, 2011
Question

Selecting a date 5 days from now in cfquery

  • January 27, 2011
  • 3 replies
  • 1686 views

I am trying to use cfschedule to send an email to a speaker who will give a presentation 5 days from now. I tried using the following code:

<CFQUERY Name="getInfo" datasource="#application.database#">
SELECT pres_id, pres_date
FROM presentations
where pres_date = #DateFormat(DateAdd("d",5,Now()),"mm/dd/yyyy")#
</CFQUERY>

Where pres_date is saved in the database in the following format: mm/dd/yyyy, ie: 2/1/2011. I can output the following: #DateFormat(DateAdd("d",5,Now()),"mm/dd/yyyy")# ,  and get the correct date, again 2/1/2011, that corresponds with the date saved in the database. But when I put the DateFormat string in the CFQUERY nothing shows when I call up the script. Any ideas why?

    This topic has been closed for replies.

    3 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 28, 2011

    rickaclark54 wrote:


    Where pres_date is saved in the database in the following format: mm/dd/yyyy, ie: 2/1/2011. I can output the following: #DateFormat(DateAdd("d",5,Now()),"mm/dd/yyyy")# ,  and get the correct date, again 2/1/2011, that corresponds with the date saved in the database.

    In addition to what PaulH has said, you should consider using

    WHERE pres_date='#DateFormat(DateAdd("d",5,Now()),"m/d/yyyy")#'

    The mask mm/dd/yyyy will give '02/01/2011', not the '2/1/2011' you expect.

    A
    Anonymous
    January 28, 2011

    Thanks for the tips guys,

    I converted the date from a string to a date/time and used the following code to pull up the speaker I wanted to send the message to:

    <CFQUERY Name="getInfo" datasource="#application.database#">
    SELECT pres_id, pres_date, contacts.contact_id, contacts.contact_first, contacts.contact_email
    FROM presentations INNER JOIN contacts ON presentations.contact_id = contacts.contact_id
    where Day(pres_date) = #Day(DateAdd("d",4,Now()))#
    </CFQUERY>

    T
    tooMuchTrouble
    Inspiring
    January 28, 2011

    On 1/28/2011 11:22 PM, rickaclark54 said:

    where Day(pres_date) = #Day(DateAdd("d",4,Now()))#

    uh, what about dates on the same day next month? next year?

    T
    tooMuchTrouble
    Inspiring
    January 28, 2011

    On 1/28/2011 5:34 AM, rickaclark54 said:

    <CFQUERY Name="getInfo" datasource="#application.database#"> SELECT pres_id,

    pres_date FROM presentations where pres_date =

    #DateFormat(DateAdd("d",5,Now()),"mm/dd/yyyy")# </CFQUERY>

    is pres_date a string or datetime datatype? if it's a string datatype (which i

    would say is generally a bad idea to store datetimes) you forgot the single quotes:

    WHERE pres_date='#DateFormat(DateAdd("d",5,Now()),"mm/dd/yyyy")#'

    ilssac
    ilssac
    Inspiring
    January 27, 2011

    Hopefully because the pres_date column in the database is a DATE type field, not a STRING type field with a string that looks like a certain date to some people.

    And comparing a date field to a string value is never going to produce a match.

    If this is a date field then using the <cfqueryparam....cf_sql_type="cfsqldate"> AND|OR  CreateODBCDate() function will allow you to give the database a true date value.

    If your database field is a string field.  Then what does the data really look like.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices