getting month from a timestamp 1

[ankursaxena]

Hi! I am maintaining a billing record in a table which contains the timpestamp for the start time and end time. now i want to be able to search for all records in this table which are from month say May,

is there a way i could do this, or may be someone knows how to convert a timestamp value and extract the month either value or in words?

it would be awesome..

thanx a lot

Ankur

 

[DumTech]

select * from your_table_name where MONTH(your_date_field)='5';

That should work for you.

Check the online documentation at mysql.com for more on date functions and calculations. Matt
matt@paperlove.org
If I can help, I will.

 

[ankursaxena]

so you are saying the your_date_field can also be substituted by a timestamp?? not the datetime field in mySQL?

but thanx a lot, this helps..

 

[KarveR]

If your datefield is e.g. 2002-05-09 , you can from this extract:
DAYOFYEAR(2002-05-09) = numircal day of year (about 129)
DAYOFWEEK(2002-05-09) = 4
DAYNAME(2002-05-09) = Thursday
MONTH(2002-05-09) = 5
MONTHNAME(2002-05-09) = May
YEAR(2002-05-09) = 2002
QUARTER(2002-05-09) = 2 ***************************************
Party on, dudes!

 

[KarveR]

Also if your timestamp is UNIX
SELECT FROM_UNIXTIME((your timestamp field),'%Y %D %M %h:%i:%s %x');
would return: '2002 9th May 20:43:30 2002'
and so
SELECT MONTH(FROM_UNIXTIME((your timestamp field),'%Y %D %M %h:%i:%s %x')); should return 5
***************************************
Party on, dudes!