I don't get any CFErrors on the screen but my CFChart is blank.
Ignoring the correct approach for a moment, the reason that happens is that you are using the incorrect cfsqltype
for the parameters. So you are actually sending different values to the database (and consequently performing a different comparison) than you are thinking. As a result, the query fails to find any matching records. That is why your chart is blank.
By using cf_sql_timestamp
you are converting the "value" into a full date/time object. However, YEAR() only returns a four digit number. So you are comparing apples and oranges. Conceptually, your query is actually doing this:
WHERE 2014 = {ts '2009-02-13 23:31:30'}
The reason it does not throw an error is that date/time values are stored as numbers internally. So you are actually comparing a small number (ie year) to a really big number (ie date/time). Obviously the date value will be much bigger, so it will almost never match the year number. Again, conceptually your query is doing this:
WHERE 2014 = 1234567890
Since cfsqltype is optional, a lot of people think it is not very important - but it is.
Validation: In addition to its other benefits, cfqueryparam validates the supplied "value", based on the cfsqltype
(date, date and time, number, etcetera). This occurs before the sql is ever sent to the database. So if the input is invalid, you do not waste a database call. If you omit the cfsqltype, or just use the default ie string, then you lose that extra validation.
Accuracy Selecting the proper cfsqltype ensures you send the correct value to the database. As demonstrated above, using the wrong type can cause CF to send the wrong value to the database.
The cfsqltype
also ensures values are submitted to the database in a non-ambiguous format the database will interpret the way you expect. Technically you could send everything to the database a string. However, that forces the database to perform implicit conversion (usually undesirable).
With implicit conversion, the interpretation of the strings is left entirely up to the database - and it might not always come up with the answer you would expect. Submitting dates as strings, rather than date objects, is a prime example of that. How will the current database interpret a date string like "05/04/2014"? As April 5th or a May 4th? It depends. Change the database or the database settings and the result 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 an equivalent type. 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.
WHERE Year(ColumnName) = <cfqueryparam value="2014" cfsqltye="CF_SQL_INTEGER">
AND Month(ColumnName) = <cfqueryparam value="11" cfsqltye="CF_SQL_INTEGER">
Now having said all that, Dan's suggestion is the better way to perform date comparisons. That paradigm is more index friendly and works regardless of whether your target column contains a date (only) or a date and time. Note the use of cf_sql_date
in his example.
cf_sql_timestamp
- sends both a date and time
cf_sql_date
- sends a date only. the time value is truncated
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…