select thursday from date in mysql

Question

How to select a day say thursday from a date in the mysql select query and use this day to select some other values in same table

select id,music_id,contest_no,today,exp_date,start_hour,start_minute,start_ampm, DAYNAME(today) as chday
from hit_music_content
where
    exp_date>='2010-07-30' and 
    today between '2010-07-30' and '2010-08-05'
limit 0,5

I want if this query is true then select all the values from hit_music_content on some day say friday but How?

Answer 1

have a look at date_format in mysql http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

%w selects a day as int (where 0 is sunday) %W selects a day as text (Sunday).

SELECT DATE_FORMAT(YOURDATEFIELD,'%W') as dataformated FROM YOURTABLE;