Skip to main content
T
trojnfn
Inspiring
June 25, 2009
Question

CREATEODBCDATE

  • June 25, 2009
  • 2 replies
  • 662 views

I have a field named docDate that is defined as date time in sql table.

I want to query the table and find all records for docDate >= 05/18/2009 and docDate <= 05/24/2009

If I have this in my query, is it correct ? It seems to run fine thru analyzer but there are too many records for me to cound manuall to verify.

WHERE CONVERT(CHAR(10), docDate, 101) between #createodbcdate(lastWeek)# and #CreateODBCDate(todaysDate)#, where lastWeek is 05/18/2009 and todaysDate is 05/24/2009

    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    June 25, 2009

    In addition to Ian's answer's there is another consideration - the time component of an mssql date field.  Unless 100% of your records have all 0s in the time part,  if you want to do this:

    I want to query the table and find all records for docDate >= 05/18/2009 and docDate <= 05/24/2009

    do this

    where docdate >= 05/18/2009 and docDate < 05/25/2009

    ilssac
    ilssac
    Inspiring
    June 25, 2009

    I would expect that the convert() function is unnecessary.  If the field is defiend as a datetime field than passing in datetime values should match without any conversion requred.

    As to passing datetimevalues while createodbcdate() will do that, using <cfqueryparam value="#lastWeek#" cfsqltype="cf_sql_date"> is considered a much better way to do this as it also protects one from sql injection as well as creating a datatime value.

    T
    trojnfnAuthor
    Inspiring
    June 25, 2009

    I would expect that the convert() function is unnecessary.  If the field is defiend as a datetime field than passing in datetime values should match without any conversion requred.

    so it will just be Where docDate between odbccretedate(lastweek) and odbcreatedate(todaydate) ?

    As to passing datetimevalues while createodbcdate() will do that, using <cfqueryparam value="#lastWeek#" cfsqltype="cf_sql_date"> is considered a much better way to do this as it also protects one from sql injection as well as creating a datatime value

    Using this method, would it be

    Where docDate betewwn  <cfqueryparam value="#lastWeek#" cfsqltype="cf_sql_date"> and  <cfqueryparam value="#todaysdate#" cfsqltype="cf_sql_date"> ?

    ilssac
    ilssac
    Inspiring
    June 25, 2009

    trojnfn wrote:

    Using this method, would it be

    Where docDate betewwn  <cfqueryparam value="#lastWeek#" cfsqltype="cf_sql_date"> and  <cfqueryparam value="#todaysdate#" cfsqltype="cf_sql_date"> ?

    Yup, that should work just fine.

    Message was edited by: Ian Skinner Assuming you spell "Between" correctly in your SQL statement.

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