CREATEODBCDATE

Report · Jun 25, 2009

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

Report · Jun 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.

Report · Jun 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"> ?

Report · Jun 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.

Report · Jun 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