Skip to main content
L
lther
Inspiring
December 23, 2009
Answered

Query a date in a varchar field

  • December 23, 2009
  • 2 replies
  • 633 views

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!

This topic has been closed for replies.
Correct answer JR__Bob__Dobbs-qSBHQ2

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>

2 replies

J
JR__Bob__Dobbs-qSBHQ2Correct answer
Inspiring
December 23, 2009

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>
L
ltherAuthor
Inspiring
December 23, 2009

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

D
Dan_Bracuk
Inspiring
December 23, 2009

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.

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