Get day of week in SQL Server 2005/2008

In SQL Server 2005/2008, finding the day of the week from a given date is straightforward, thanks to built-in date functions. This is often useful when you need to analyze data by weekdays, generate reports, or apply business logic based on days.

Here are the most common ways to get the day of the week:

Using DATEPART()

   SELECT DATEPART(WEEKDAY, GETDATE()) AS DayNumber;

• This returns a number between 1 and 7 (depending on your server’s SET DATEFIRST setting).
  
• For example, if DATEFIRST is set to 7 (Sunday), then Sunday = 1, Monday = 2, etc.
  

Using DATENAME()

   SELECT DATENAME(WEEKDAY, GETDATE()) AS DayName;

• This returns the name of the day, like Monday or Friday.
  
• Very useful when you want a human-readable result in reports.
  

Combining both

 SELECT 
    DATEPART(WEEKDAY, GETDATE()) AS DayNumber,
    DATENAME(WEEKDAY, GETDATE()) AS DayName;

This gives you both the numeric value and the actual weekday name.

 Key points to remember:

• DATEPART(WEEKDAY, date) → numeric representation of the day.
  
• DATENAME(WEEKDAY, date) → full name of the weekday.
  
• The starting day of the week depends on your DATEFIRST setting (default is Sunday in US English).
  

In short, you can easily get either the day number or day name in SQL Server 2005/2008 using DATEPART() or DATENAME(). Both are reliable depending on whether you need a numeric or text format.