SELECT *
FROM closed_tickets
WHERE MONTH(closed_date) = #month#
AND YEAR(closed_date) = #dateFormat(theMonth," yyyy")#
AND technician_id = #techID#
This is what I tried:
SELECT *
FROM closed_tickets< br /> WHERE MONTH(closed_date) =
AND YEAR(closed_date) = #dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP">
AND technician_id =
When I change the query to this time, it will destroy my CFChart in some way. I didn’t I see any CFErrors on the screen, but my CFChart is blank.
I narrowed it down to be relevant in my query:
#dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"
When I delete this parameterized part of the query, and then put it
#dateFormat(theMonth,"yyyy")#
Useful.
p>
Can anyone know something about this?
I don’t get any CFErrors on the screen but my CFChart is blank.
Ignore the correct approach for now, it happened because you used an incorrect cfsqltype parameter. Therefore, you are actually sending a different value to the database (so performing a different comparison ) Instead of what you think. Therefore, the query cannot find any matching records. This is why your chart is blank.
By using cf_sql_timestamp, you convert the “value” to a complete date/ Time object. However, YEAR() only returns a four-digit number. So you have to compare apples and oranges. Conceptually, your query actually does this:
< pre>WHERE 2014 = {ts ‘2009-02-13 23:31:30’}
The reason why the error is not thrown is that the date/time value is internally stored as a number. So you actually A small part (i.e. year) is compared with a very large number (i.e. date/time). Obviously the date value will be much larger, so it will almost never match the year number. Again, conceptually, your query This is done:
WHERE 2014 = 1234567890
Since cfsqltype is optional, many people think it is not very important – but it is.< /p>
>Validation: In addition to other benefits, cfqueryparam also validates the provided “value” according to cfsqltype (date, date and time, numbers, etc.). This happens before the sql is sent to the database. Therefore, if you enter Invalid, the database call will not be wasted. If you omit the cfsqltype, or just use the default ie string, you will lose additional verification.
>Accuracy Selecting the correct cfsqltype ensures that the correct value is sent to the database. As above As mentioned, using the wrong type will cause CF to send the wrong value to the database.
cfsqltype also ensures that the value is submitted to the database in a non-ambiguous format, and the database will interpret it in the way you expect. Technically , You can send everything to the database. However, this will force the database to perform implicit conversion (usually undesirable).
Through implicit conversion, the character The interpretation of the string depends entirely on the database-and it may not always give the answer you expect. Submitting a date as a string instead of a date object is a good example. How the current database interprets date strings, such as “05 /04/2014”? April 5th or May 4th? It depends. Changing the database or database settings, the results may be completely different.
The only way to ensure consistent results is to specify the appropriate cfsqltype. It should match the data type of the comparison column/function, or at least equal to etc. Effective type matching. In the case of YEAR(), it returns a four-digit number. So you should use cf_sql_integer, as Adrian mentioned the comments. The same applies to your MONTH() comparison.
< /p>
WHERE Year(ColumnName) =
AND Month(ColumnName) =Now that we have said so much, Dan's suggestion is a better way to compare dates. Whether your target column contains date (only) or date and time, That paradigm is more index-friendly. Please note that in his The example uses cf_sql_date.
> cf_sql_timestamp – send date and time> cf_sql_date – send only the date. The time value is truncated
I have a query, I ran to populate the CFChart I was trying to parameterize:
SELECT *< br /> FROM closed_tickets
WHERE MONTH(closed_date) = #month#
AND YEAR(closed_date) = #dateFormat(theMonth,"yyyy")#
AND technician_id = #techID#
This is what I tried: