TO_CHAR and TO_DATE question

Hi,

    I am trying to create some summary tables for lots of collected data.
For many devices , I collect a number of stats every 5 mins. I have created Hourly,Daily and Monthly tables Source table is COLDATA
Hourly -> COLDATAHOURLY
Daily -> COLDATADAILY
Monthly->COLDATAMONTHLY

I have made the following scripts to transfer the data from the master table into the summary tables.

Hourly Script

insert into coldatahourly (ipaddr,instance,varid,collecttime,floatvalue) select
ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy HH24'),round(avg(floatvalue))
from coldata group by
ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy HH24');

Daily Script

insert into coldatadaily (ipaddr,instance,varid,collecttime,floatvalue) select
ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy'),round(avg(floatvalue))

from coldata group by
ipaddr,instance,varid,to_char(collecttime,'dd-Mon-yy');

Monthly Script

insert into coldatamonthly
(ipaddr,instance,varid,collecttime,floatvalue) select ipaddr,instance,varid,to_char(collecttime,'Mon-yy'),round(avg(floatvalue))

from coldata group by
ipaddr,instance,varid,to_char(collecttime,'Mon-yy');

This works fine until I get to the Monthly one, where the sort order will have to be yyyy-Mon instead of Mon-yyyy, otherwise it won't group properly.
The problem I have is that I am unable to get a TO_DATE working on the insert section of the statement, no matter what I do, I get an error back telling me that there is a comma missing, I have tried every combination of TO_DATE and keep getting the same error, if I can't format the date going in the insert, the statement fails telling me that I am trying to insert an invalid date.

The column in question here is named COLLECTTIME (DATE type), all other columns are OK.

Can anyone give me an example of how I might better create and run these scripts and use the TO_DATE function to format the data coming into the COLLECTTIME field

Your help would be much appreciated.

Mark :-) Received on Tue Jun 08 1999 - 05:47:47 CDT