Get string representation of a sql DateTime day

Question

Hi all,

Let's say i have a sql datetime of '1 May 2009' or '12 May 2009'. Is there any built in sql function / operation i can perform on the dates above to return the string representation of the DAY of the date?

So for '1 May 2009' i'll get "Friday" as the answer (case not important). For '12 May 2009' i'll get "Tuesday".

Answer 1

DATE_FORMAT(somedatetimevariable, '%W');

Ref: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Edit (gbn): For MySQL

Answer 2

DATENAME

SELECT DATENAME(weekday, '1 May 2009')

Edit: For MS SQL Server

Answer 3

For MySQL, if the column is literally a string, you have to convert it to a DATE first:

mysql> SELECT DATE_FORMAT(STR_TO_DATE('1 May 2009', '%e %M %Y'), '%W');
+----------------------------------------------------------+
| DATE_FORMAT(STR_TO_DATE('1 May 2009', '%e %M %Y'), '%W') |
+----------------------------------------------------------+
| Friday                                                   |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Answer 4

If you are looking at how to make different representations, you can do no better than this link here from SQL Server Helper. This will show you how to convert dates into more formats than you'll know what to do with. I have this constantly bookmarked for easy reference.