Query a date in a varchar field

[lther]

I am trying to do a simple query...

Count the number of records in a particular month...

I use variables in the URL and use this query (in MSSQL)

SELECT recorddate FROM sometable
WHERE Year(recorddate) LIKE '#Url.year#'
AND Month(recorddate) LIKE '#Url.month#'

This should work in theory, I believe, but the problem is that the date field is varchar (though all records have a timestamp which has been added as:

<INPUT type="hidden" name="recordate" value="#Now()#">

I'm not that advanced in ColdFusion so I'm hoping someone can help me out... Is there a simple way to do this?

Thanks!

[Dan_Bracuk]

The short answer is to store dates as dates, not as text.

For your current situation, hopefully all your records have the same format.  If so, you can use wildcards in the appropriate places.  You can't use date functions on text though.

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

I agree with Dan Bracuk that you should store your date values in a date column.  For SQL server this would be DATETIME, SMALLDATETIME, or DATE depending on your server version and requirements.

As a quick fix you can use a derived table that converts your varchar column values to dates.  Be aware that this should only be used as work around until you can update your table to use a date column.  This query will probably not perform well on large data sets.  You should also use the CFQUERYPARAM tag to avoid SQL injection vulnerabilities.

<cfquery name="report" datasource="mydsn">
    SELECT recorddate 
     FROM
     (
         SELECT CONVERT(DATETIME, recorddate) AS recorddate 
         FROM sometable
     ) AS Q
     WHERE Year(recorddate) = <cfqueryparam value="#url.year#" cfsqltype="cf_sql_integer" />    
         AND Month(recorddate) = <cfqueryparam value="#url.month#" cfsqltype="cf_sql_integer" />   
</cfquery>

[lther]

Wow thanks to both of you! Question was answered and I learned a couple new things along the way!