How to display day from date

Question

Using SQL Server 2005

Table 1

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

Date Column Type: DATETIME .

Expected Result

 Monday Tuesday 

How to make a request for a day ...

Answer 1

You can use the DATENAME function.

 SELECT DATENAME(WEEKDAY,[Date]) FROM Table1 

Answer 2

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

 SELECT DATENAME(dw, DateField) FROM Table 

In earlier versions, the closest you could be to the DATEPART function with dw as the interval, but you need CASE to convert the number returned to the day name, as shown 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 look at the MSDN documentation notes for these date functions for information about which day of the week is considered the first day (depends on the DATEFORMAT parameter of your SQL environment).

Answer 3

Here is a link for DateName to help you

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

If you need a digital link like Sunday = 1, Saturday = 7, use DatePart

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

Is this what you want

 DateName(dw, [Date]) 

Answer 4

I would suggest setting the language before getting the day of the week

 SET LANGUAGE 'Italian' SELECT DATENAME(WEEKDAY,GETDATE()) 

Answer 5

 select name from table where weekday(column_name = 'monday'); 

He prints the one born on Monday, he displays his names