SQL Query not return date if we use <> operators

[francois-yanick]

Hi,

I am in a way at this moment where I have to find records from tables where
date are the primary field to be validated. In another word, I tried to
recuperate records by using StartingDate >= '#myDate#' but it seems that the
MS-SQL Server never return any records. But if I tried from the MS-SQL
Enterprise Manager (Query), it returns records. So it is between ColdFusion MX
(I use the Version 7 in Developer edition and Standard Edition) and ODBC. But I
have to admit that I am completely confused by this issue.

I also tried to create a SQL Procedure and use the CFPROCEDURE but without any
success.

Thank you for any help! And if you need a little bit more explication, I will
be glad to supply more.

<cffunction name="fConvertToCADHebdo">
<cfargument name="currencyid" required="true">
<cfargument name="amount" required="true">
<cfargument name="dtExpense" required="true">
<cftrace text="CurrencyID = #currencyid#">
<cftrace text="amount = #amount#">
<cftrace text="dtExpense = #dtExpense#">
<cfset vReturn = "">
<cfquery name="qGetCurrencyRates" datasource="#Application.DSN#">
SELECT *
FROM currencyConversion
WHERE StartingDate >= <cfqueryparam value="#dtExpense#"
cfsqltype="cf_sql_date">
ORDER BY StartingDate ASC
</cfquery>
<cfdump var="#qGetCurrencyRates#" label="qGetCurrencyRates">
<cfif qGetcurrencyRates.RecordCount gt 0>
<cftrace text="qGetCurrenctRates.RecordCount is gt 0">
<cfquery name="qGetRates" datasource="#Application.DSN#">
SELECT *
FROM currencyConversion
WHERE id = #qGetCujrrencyRates.id[1]#
</cfquery>
<cfif qGetRates.RecordCount gt 0>
<cftrace text="qGetRates.RecordCount gt 0">
<cfset vReturn = qGetRates.id>
</cfif>
</cfif>

<cfreturn vReturn>
</cffunction>

<cfset vExpenses = 200.99>
<cfset vExpenseCurrencyID = 2>
<cfset vExpenseDate = '2/4/2005 00:00:00'>


<cfset vTemp = fConvertToCADHebdo(vExpenseCurrencyID, vExpenses, vExpenseDate)>
<cfoutput>#vTemp#</cfoutput>

[The ScareCrow]

I have always had trouble using cf_sql_date in cfqueryparam, I have always had
to use cf_sql-timestamp

But this has been in cf 5

I would suggest (for testing)
change the where clause to
WHERE StartingDate >= #CreateODBCDate(dtExpense)#

If this works correctly, then try cahning the cfsqltype in the cfqueryparam
and see if that works.

Ken

[francois-yanick]

Thank's for your propositions but - unfortunately - it does not working. But,
here is the results:

From the method with CreateODBCDate

<cfquery name="qGetCurrencyRates" datasource="#Application.DSN#">
SELECT *
FROM currencyConversion
WHERE StartingDate >= #CreateODBCDate(dtExpense)#
ORDER BY StartingDate ASC
</cfquery>

The result from the output/debug was that:

SELECT *
FROM currencyConversion
WHERE StartingDate >= {d '2005-02-04'}
ORDER BY StartingDate ASC

And for the method with CFQUERYPARAM, the result of the output was that:

SELECT *
FROM currencyConversion
WHERE StartingDate >= ?
ORDER BY StartingDate ASC

So, I feel that the CreateODBCDate function was the closest one but how to
relieve of the '{d' and the ending '}'?

[The ScareCrow]

So I assume that there was no recordset returned ?

If the column "StartingDate" is of data type datetime in the database, then
the following is correct.

SELECT *
FROM currencyConversion
WHERE StartingDate >= {d '2005-02-04'}
ORDER BY StartingDate ASC

Ensure that there are records in the db that match this criteria.

What was the query that you tried in query analyser ?

Ken

[francois-yanick]

The query that I tried with success in Enterprise Manager was this one:

SELECT *
FROM currencyConversion
WHERE StartingDate >= '2/4/2005'
ORDER BY StartingDate ASC

When I tried it from Enterprise Manager, it returns me few records so I assume
it have to work!

[francois-yanick]

I just noticed that the database record the date in this format 'm/d/yyyy'
instead of 'yyyy-mm-dd' like what CreateODBCDate created. I remember that I
have a problem in the past with the format of the date when I was using it to
search the database. But, usually, I will receive an error message from
ColdFusion which I do not have with the method of CreateODBCDate. But I think
it is better to add this one as a remark in case it serve!

[The ScareCrow]

The fact that sql server displays the date as m/d/yyyy should not make any
difference. As the date is not stored like this internally.

So the code you have should work.

Just a thought, but have you installed the sp3 for sql server ?

If not this could be the problem.

Otherwise, I'm afraid I can't help.

Ken

[J?J]

I too am finding it impossible to sort by date in my Acccess Database.

<cfquery datasource="ranger" name="StoryQuery">
Select *from stories where date > #01/01/2001#
</cfquery>

Creates an error
and

<cfquery datasource="ranger" name="StoryQuery">
Select *from stories where date > #createodbcdate(01/01/2001)#
</cfquery>

Is processed as

Select * from stories where date > {d '1899-12-30'}

Which obviously isn't correct. Dateformat() does not change the results.

any help?

[The ScareCrow]

You get errors because you have the incorrect format

The first should be as follows, notice the single quotes
<cfquery datasource="ranger" name="StoryQuery">
Select *from stories where date > '#01/01/2001#'
</cfquery>

The second should be, notice the double quotes
<cfquery datasource="ranger" name="StoryQuery">
Select *from stories where date > #createodbcdate("01/01/2001")#
</cfquery>

Ken

[OldCFer]

You need quotes around string dates passed to Date functions.
Select *from stories where date > #createodbcdate('01/01/2001')#
Otherwise it gives you a date corresponding to the division result of 1/1/2001

Francois, you're not trying to put dates into the db in "dd/mm/yy" format are
you?

[francois-yanick]

I am not trying to push a year in two digits.

But I got the solution. The final answer for that problem (only for MS SQL
issue, not for Access) is to make sure that the MS-SQL Service Pack 3 is
installed. After that, everything will be alright for this problem.