Hello guys,
I think I need a little more help ...
For the query below, I need to get data from last month. Right now, everytime I have to change the query (the dates at the bottom), and then run it to get the right results. I just came across this thread, and it seems like it can be of great help for me. However, I am not able to make it work with the two solutions above. All I want is first date of previous month as the first evttime, and last day of previous month as the higher limit. I know there is a way in MS SQL, getdate(), but it does not work in mysql... and ...

I will appreciate if anyone can help me with that.

select s.sic as "SIC Code", s.cat_name as "SIC Name", count(*) as 'Requests'
from clientservices.p417_caller_revenue_summary r, pmartin.category_names s
where r.evtcode = 4
and r.evtnumber = s.sic
and evttime between unix_timestamp(20110201)
and unix_timestamp(20110301)
group by s.sic

Thanks again.

umermariner

What is the datatype for evttime? The UNIX_TIMESTAMP returns an unsigned integer of the number of seconds since 01 Jan 1970. However, its parameter must be a valid date, datetime or timestamp value. Look at STR_TO_DATE function which will allow you to format your date correctly.

So UNIX_TIMESTAMP(STR_TO_DATE('20110201','%Y%d%m')) is most likely what you are looking for.