Mysql Converting date to days of week

Question

I have the following query which gives me the visit_date for each client:

select visit_date from visit

The output of the above query is the timestamp when the entry was inserted into the Database , it will appear as follows :

visit_date
2013-12-21 06:31:04
2013-12-21 11:05:30
2013-12-21 23:03:12

How can I convert the above date to days of the week in MySQL?

Answer 1

Check MySQL DATETIME FUNCTIONS to fetch day of week or day name or day of month or day of year

Try this:

SELECT DAYOFWEEK(visit_date) FROM visit;
SELECT DAYNAME(visit_date) FROM visit;
SELECT DAYOFMONTH(visit_date) FROM visit;
SELECT DAYOFYEAR(visit_date) FROM visit;

Answer 2

SELECT DAYOFWEEK(visit_date) FROM visit

it returns the weekday index for date (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

Answer 3

You can alter the date with plain php.

date_format($visit_date, 'formatstring');

l (lowercase 'L')
A full textual representation of the day of the week
Sunday through Saturday

phpdoc

Watch out with locale stored on the server of course if your site should be multilang.