Get Day of Week in SQL Server

SQL Server 2008 introduced DATENAME function that return name of datepart of the specified date.
 
Syntax
 
DATENAME ( datepart , date )

Example
  1. SELECT GETDATE() DAte_, DATENAME(dw,GETDATE()) as DayName_  
  2. UNION ALL  
  3. SELECT DATEADD(DAY,1,GETDATE()) DAte_, DATENAME(dw,GETDATE()+1) as DayName_  
  4. UNION ALL  
  5. SELECT DATEADD(DAY,2,GETDATE()) DAte_, DATENAME(dw,GETDATE()+2) as DayName_  
  6. UNION ALL  
  7. SELECT DATEADD(DAY,3,GETDATE()) DAte_, DATENAME(dw,GETDATE()+3) as DayName_  
Output
 
 
 
In this example we use "dw" to get the Day Name but you can use "mm" to get the month name and "yy" to get the year value.
 
Example