Skip to main content
A
Anonymous
December 22, 2009
Question

Recordset where date is 90 days or older.

  • December 22, 2009
  • 1 reply
  • 923 views

Hello,

I am trying to build a recordset from a MSSQL database of records where c.date is 91 days or older.  It's been 3 years since I touched Coldfusion so my code below is a little rusty....

<cfset todayDate = Now()>

<!--- Populate Open Quotes --->
<cfquery name="Quotes" datasource="quote">
SELECT q.qid, q.cdate, q.name, q.region, q.cuid, q.quid, q.status, c.company,
c.sitecity
FROM quote q, customer c
WHERE q.status = 'open' AND q.cuid = c.uniqueid AND q.cdate < (<cfoutput>#todaydate#</cfoutput>-90)
ORDER BY cdate
</cfquery>

Any help on this would be greatly appreciated.....

Thanks In Advance.

Gary

    This topic has been closed for replies.

    1 reply

    -
    -__cfSearching__-
    Inspiring
    December 22, 2009

    There are a few ways to do it. My preference is to use the DateAdd() function to calculate the date 90 days ago:  #DateAdd("d", -90, now())#

    Of course that will also include the time as well:  2009-12-21 17:21:00.  You can truncate the time (ie convert it to a date only) by using either the CreateODBCDate() function:

    WHERE q.cdate < #CreateODBCDate(DateAdd("d", -90, now()))# 

    ... OR by using cfqueryparam with the data type "cf_sql_date"

    WHERE q.cdate <    <cfqueryparam value="#DateAdd("d", -90, now())#" cfsqltype="cf_sql_date">

    Generally cfqueryparam is the better option for databases like MS SQL because a) it uses bind variables to help improve performance b) helps protect against sql injection. It also has some other nice features like the "list" and "null" attributes.

    http://www.adobe.com/devnet/coldfusion/articles/sql_injection.html

    (<cfoutput>#todaydate#</cfoutput>-90)

    BTW: You do not need to use <cfoutput> tags inside a cfquery. There may be exceptions, but generally any #variable# used inside a CF tag will be evaluated automatically (without cfoutput tags).

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    December 22, 2009

    I agree with cfsearching the CFQUERYPARAM should be used.  Note that the cfsqltype attribute should be CF_SQL_TIMESTAMP, rather than CF_SQL_DATE, for Microsoft SQL Server datetime columns.

    CFQUERYPARAM

    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474

    -
    -__cfSearching__-
    Inspiring
    December 22, 2009

    Note that the cfsqltype attribute should be

    CF_SQL_TIMESTAMP, rather than CF_SQL_DATE, for Microsoft SQL

    Server datetime columns.

    Yes, that is what the documentation says. Though I am a bit leary of blindly trusting it. In my experience MS does accept cf_sql_date as well. It is also important to note there is a difference. If you use cf_sql_timestamp, then both the date and time are preserved and passed to the database. However, if you use cf_sql_date the time portion will be truncated, which what is desired here.

    -Leigh

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