MYSQL: Extract Date from Timestamp Field

I have a table that has only two fields: dtmDateTime timestamp field; fData float field.  I want to group data by max, min, avg by date.  How can I do this via SQL SELECT.  I tried select convert(varchar,dtmdatetime,104) from tablename; but it does not work - error is returned for convert.  Any help extracting the date from the timestamp field would be appreciated
LVL 1
DCCoolBreezeAsked:
Who is Participating?
 
fReAkCoRpConnect With a Mentor Commented:
To select the Date from the filed you can use the Date Function you can used like this example:
SELECT date(dtmDateTime) as DateField,fData as DataField FROM test t;
for the max , min and avg you can go from this examples:
Group by :
SELECT date(dtmDateTime) as DateField,fData as DataField, count(*) as CountField FROM test t group by DateField
MAX and MIN
SELECT min(date(dtmDateTime)) as DateField, fData as DataField, count(*) as CountField FROM test t
group by DataField
for and avg you can go like this
SELECT sum(fData)/count(*) as from FROM test t where date(dtmDateTime)=date('2001-01-01');
you can use the sum function and add all the fData and divided by the count of data that matches the date you want to have your query based on.
this are just some examples but if you can post more info in what are you going to do maybe I can help you more.
Hope this works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.