You can hunt down the column definitions in the INFORMATION_SCHEMA. For example, for the table mydb.mytb and the column name needed is called mycol, do this:
SELECT column_type FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytb'
AND column_name='mycol';
For the query with formulas that you have in your question:
SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;
Try creating your own empty temp table using your query with a FALSE-evaluated WHERE clause:
CREATE TABLE IF NOT EXISTS test.mycolumnmap
SELECT id, FLOOR(date_id / 100) month_id, SUM(value) value FROM some_table WHERE 1=2;
That table, though empty, has a definite table structure and some columns types defined. Now, just run the same query to get the column types from the temp table:
SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='mycolumnmap'
AND column_name IN ('id','month_id','value');
Another alternative is to just ask the INFORMATION_SCHEMA about the columns of the view directly:
SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='view_name';
Give it a Try !!!
Best Answer
Just try the following command.
Non-quoted numeric values can be converted to money by casting the numeric value to text and then money