How to display a day from the Date

Question

Using SQL Server 2005

Table1

Date

19-12-2009
20-12-2010
.....

Date Column datatype is DATETIME.

Expected Output

Monday
Tuesday

How to make a query for getting the day...

Solution 1

You can use the DATENAME function.

SELECT DATENAME(WEEKDAY,[Date])
FROM Table1

Solution 2

As @Lamak suggested, you can use the DATENAME function if you're on SQL Server 2005 or later:

SELECT DATENAME(dw, DateField) FROM Table

On earlier versions, the closest you could get was the DATEPART function with dw as the interval, but you needed a CASE to convert the number returned to the day name, as seen here.

SELECT CASE DATEPART(dw, DateField)
       WHEN 1 THEN 'Sunday'
       WHEN 2 THEN 'Monday'
       WHEN 3 THEN 'Tuesday'
       WHEN 4 THEN 'Wednesday'
       WHEN 5 THEN 'Thursday'
       WHEN 6 THEN 'Friday'
       WHEN 7 THEN 'Saturday'
       END AS DayOfWeek
FROM Table

Also note the remarks on the MSDN documentation for those date functions, for information about specifying which day of the week is considered the first day (depends on the DATEFORMAT setting of your SQL environment).

Solution 3

Here is the reference for DateName which will assist you

http://msdn.microsoft.com/en-us/library/ms174395.aspx

If you want a numerical Reference like Sunday = 1, Saturday = 7, then use DatePart

http://msdn.microsoft.com/en-us/library/ms174420.aspx

This is what you want

DateName(dw, [Date])

Comments

[CLAbeel]

If you are interested in answering questions here, please learn to use code formatting.