Problem grouping by date - Informix SQL 1

[Malekish]

I hope someone can give me a hand while I'm learning the ins-and-outs of Informix. I have a table with a bunch of data including dates in Datetime Year to Minute format. I want to run a query and sum it up by date, I'm struggling with it's wonky time formats.

select date(d_time), sum(somefield)
from my.table
where d_time > datetime(2008-12-01) year to day
group by 1

My data looks something similar to

2008-12-01  8:01     12
2008-12-01 12:15      7
2008-12-01 14:08     15
2008-12-02 10:50     10

So I'm expecting output like

2008-12-01           34
2008-12-02           10

Instead I'm getting this output

2008-12-01      12
2008-12-01       7
2008-12-01      15
2008-12-02      10

I've tried select distinct date(d_time) and several other ideas but I keep getting lots of rows as it's grouping by those individual times of the day and not the day itself.

I'm sorry but I do not know the exact version of Informix, I have ODBC access to the machine only.

 

[PHV]

What about this ?

SELECT MDY(MONTH(d_time),DAY(d_time),YEAR(d_time)), SUM(somefield)
FROM yourTable
WHERE d_time > '2008-12-01 00:00'
GROUP BY 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Malekish]

That did it! Thank you (and now to save that for future usage)