Dateadd to column in select statement

Question

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*** · Answer

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=ColdFusion_Documentation&file=00000422.htm

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=ColdFusion_Documentation&file=00000600.htm

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded