Skip to main content
A
Anthony Spears
Inspiring
January 23, 2008
Question

Different result from same SQL statement

  • January 23, 2008
  • 7 replies
  • 1054 views
The following SQL statement brings back records using query analyzer on the SQL server. However when I run it in a cold fusion page it comes back with no results. Any idea why????????

SELECT COUNT(h.userID) AS hits, u.OCD
FROM dbo.tbl_hits h INNER JOIN
dbo.tlkp_users u ON h.userID = u.PIN
WHERE (h.appName LIKE 'OPwiz%') AND (h.lu_date BETWEEN '05/01/07' AND '06/01/07')
GROUP BY u.OCD
ORDER BY u.OCD
This topic has been closed for replies.

7 replies

A
Anthony SpearsAuthor
Inspiring
January 23, 2008
Yes, I am 100% sure. But we have noticed something else. I stated earlier that when we used different dates that we got back the same results. That is not the case. Some records show up in the cold fusion results that don't show up in the SQL analyzer and vice versus..????????
N
Newsgroup_User
Inspiring
January 23, 2008
Ian Skinner wrote:
> .... BETWEEN <cfqueryparam value="05/01/07" cfsqltype="cf_sql_date"> AND
> ...

OR maybe that should be:

... BETWEEN <cfqueryparam value="#createODBCDate('05/01/07')#"
cfsqltype="cf_sql_date"> ...


A
Anthony SpearsAuthor
Inspiring
January 23, 2008
That didn't work either.

But here is something interesting. If we use the dates 05/01/2007 and 06/01/2007 we get results in SQL Server Query Analyzer but not using a cold fusion page. But if we use the dates 05/01/2007 and 09/01/2007 both get back the same results.
N
Newsgroup_User
Inspiring
January 23, 2008
Anthony Spears wrote:
> I got back records when I took out the AND (h.lu_date BETWEEN '05/01/07' AND '06/01/07').
>
> Does this give you any more clues??
>
> Thanks in advance.

What happens if you tell your systems those strings are dates?

... BETWEEN <cfqueryparam value="05/01/07" cfsqltype="cf_sql_date"> AND ...
D
Dan_Bracuk
Inspiring
January 23, 2008
It tells me that not only are you using strings instead of dates, you are using the most ambiguous format available.
A
Anthony SpearsAuthor
Inspiring
January 23, 2008
I got back records when I took out the AND (h.lu_date BETWEEN '05/01/07' AND '06/01/07').

Does this give you any more clues??

Thanks in advance.
A
Anthony SpearsAuthor
Inspiring
January 23, 2008
Sorry, this did not work. But thanks.
P
paross1
Participating Frequently
January 23, 2008
Did you verify that it is actually the BETWEEN statement that is actually causing this to not return any rows? In other words, do you get any rows returned just using WHERE (h.appName LIKE 'OPwiz%')?

Also, are you using variables for your parameters when testing from ColdFusion verses constants when testing with query analyzer? If you are using variables from ColdFusion, did you try to substitute them with the same constant values that work in query analyzer?

Phil
P
paross1
Participating Frequently
January 23, 2008
Perhaps SQL Server is performing an implicit type conversion with your date values to a date/time datatype when the query is run via the query analyzer, but that conversion is not happening from ColdFusion. What happens if you explicitly CAST() your date "strings" to datetime?

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