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?

Just to clarify the query gives you more than one date per person, or is this for three different users? — The Humble Rat, Dec 23 '13 at 09:39

Saharsh Shah · Answer 1 · 2013-12-26T04:51:18.563

5

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;

edited Dec 26 '13 at 04:51

answered Dec 23 '13 at 09:41

Saharsh Shah

28,687
8
48
83

score 1 · Answer 2 · answered Dec 23 '13 at 09:41

1

SELECT DAYOFWEEK(visit_date) FROM visit

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

answered Dec 23 '13 at 09:41

fthiella

48,073
15
90
106

score 0 · Answer 3 · answered Dec 23 '13 at 09:47

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.

Mysql Converting date to days of week

3 Answers3