Concat two fields to create a date

Hello everyone,

I'm having problems with a query that combines two int(11) fields to make a date. The query works when run directly in MySQL, but it seems to not be understood by ColdFusion. Here's the line that I'm having problems with:

Date_Format(CONCAT(dataYear,'-',dataMonth,'-01'), '%Y-%m-%d') BETWEEN '2005-04-01' AND '2005-08-01'

I think ColdFusion is turning the concat portion into a string and not considering it as a date. Therefore, the query code fails. Can someone help me with this?

ColdFusion shouldn't be doing anything to the query, but it could be a JDBC driver issue. All CF does is evaluate things between pound signs (variables, function calls, etc.), so it isn't doing anything to the code that you posted. Remember that all CF does is take the sQL statement and feed it to the data source via the JDBC driver...it doesn't "do" anything to the SQL statement in terms of processing.

I thought the same thing, but I've run into this on another occassion where I had to extract the hour of day from a datetime field. I had to use Hour() instead of DateFormat(date, '%H') because ColdFusion was expecting a different datatype. Once I made the change, then my calculations in my CF application began to work.

So, I'm thinking this is a similar thing, but I'm not sure how to resolve it. I've run the code a number of different ways directly in MySQL, and it runs properly. Here's the entire query for you to checkout. Maybe you can evaluate it and give me more feedback.
*************

SELECT CASE
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'ARS' THEN 'Archive Rec Sound'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'ART' THEN 'Art'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'BIO' THEN 'Bio'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'BRN' THEN 'EarthSci'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'CHM' THEN 'Chem'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'EAL' THEN 'East Asia'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'EDU' THEN 'Educ'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'ENG' THEN 'Eng'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GAS' THEN 'ASRG'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GHD' THEN 'HDIS'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GHG' THEN 'HRG'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GRN' THEN 'IC'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GLO' THEN 'Loan'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GMM' THEN 'Media Micro'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GSP' THEN 'Spec'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GSD' THEN 'SSDS'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'GSR' THEN 'SSRC'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'HOP' THEN 'Hopkins'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'LAN' THEN 'Lane'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'LAW' THEN 'Law Library'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'MAT' THEN 'Math'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'MUS' THEN 'Music'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'PHY' THEN 'Physics'
WHEN SUBSTRING(RS.AggregateID, 1, 3) = 'SAL' THEN 'SAL'
END AS 'Library',
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '01', RS.Count,0 ) ) AS Directional,
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '02', RS.Count,0 ) ) AS Inquiry,
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '03', RS.Count,0 ) ) AS Strategy,
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '04', RS.Count,0 ) ) AS Tutorial,
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '05', RS.Count,0 ) ) AS ResAssistance,
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '12', RS.Count,0 ) ) AS Reference,
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '13', RS.Count,0 ) ) AS TechAssistance,
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '01', RS.Count, 0 )) +
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '02', RS.Count, 0 )) +
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '03', RS.Count, 0 )) +
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '04', RS.Count, 0 )) +
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '05', RS.Count, 0 )) +
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '12', RS.Count, 0 )) +
SUM(IF ( SUBSTRING( RS.AggregateID, 8, 2 ) = '13', RS.Count, 0 ))
AS Total
FROM ReferenceStatistics RS
WHERE SUBSTRING(RS.AggregateID, 8, 2) NOT IN ('00','07','08','09')
AND (
(
Date_Format(CREATED_DT, '%Y/%m/%d') BETWEEN '2005/01/01' AND '2005/08/01'
AND InputMethod = 2
)
OR
(
DATE_FORMAT(concat(dataYear,'-',dataMonth,'-01'), '%Y-%m-%d') BETWEEN '2005-01-01' AND '2005-08-01'
AND InputMethod = 1
)
)
GROUP BY CASE
WHEN SUBSTRING(RS.AggregateID, 1, 3) <>'' THEN SUBSTRING(RS.AggregateID, 1, 3)
END
WITH ROLLUP;
*************************

Thanks

That's the thing, hour() and dateFormat() are both ColdFusion functions. The functions you are using in your query are MySQL functions. So there's nothing that CF would be doing to the SQL statement besides sending it, as it is, to the JDBC connection.