Get week day name from a given month, day and year individually in SQL Server

Tested and works on SQL 2005 and 2008. Not sure if this works in 2012 and later.

The solution uses DATENAME instead of DATEPART

select datename(dw,getdate()) --Thursday
select datepart(dw,getdate()) --2

This is work in sql 2014 also.

You need to construct a date string. You're using / or - operators which do MATH/numeric operations on the numeric return values of DATEPART. Then DATENAME is taking that numeric value and interpreting it as a date.

You need to convert it to a string. For example:

SELECT (
  DATENAME(dw, 
  CAST(DATEPART(m, GETDATE()) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(d, myDateCol1) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(yy, getdate()) AS VARCHAR))
  )