Dateadd to column in select statement

[kiyomi]

I'm querying a MySQL database that contains a varchar field storing a serial date from excel (so 6/20/2010 appears as 40347).  I have no control over the database table or its construction.

I want to do a select statement that groups the lines by month and contains a where clause limiting the rows to "this year".

I've tried select str_to_date(CallDate,'%m/%d/%Y') as NewCallDate but it returns an empty string.

I've tried select Dateadd('d', voicedata.CallDate, '01/01/1900') as NewCallDate but it returns an error saying Element CALLDATE is undefined in VOICEDATA.

Any suggestions on what else to try?

TIA,

Kiyomi

[A***]

Please note that the following suggestion is a cumbersome process.

• First query your original database to get the data.

• As you select the date column, format the value into a date object.

http://www.adobe.com/livedocs/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=Co...

• Loop through your data, building it into a fresh query.

• In the new query, ensure you define the data type for your columns especially the date column.

In ColdFusion versions MX 7 and above, QueryNew() and QueryAddColumn() both allow for explicit data type declarations.

http://www.bennadel.com/blog/729-Always-Define-Your-ColdFusion-Query-Column-Types.htm

http://www.adobe.com/livedocs/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=Co...

• Use query of queries to get your clean data with desired grouping and where clause.

[kiyomi]

Unfortunately, CreateDate requires year, month, day parameters and I can't extract those from the column.

However, your idea of creating a new query and populating it by looping from the first query did work!  I was able to use the DateAdd in the value for the QuerySetCell.

It does take a few extra seconds to run but it is doing what I need.  Thanks so much!

-K