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

I am trying get a day name like friday, saturday, sunday, monday etc from a given date. I know there is a built in function which returns the day name for example:

SELECT DATENAME(dw,'09/23/2013') as theDayName 

this SQL query returns:

'Monday'

This is all OK. But I would like to pass Month, Day and Year individually.

I am using the builtin DATEPART function to retrieve month, day and year from a date so I can pass it to the DATENAME function:

SELECT DATEPART(m, GETDATE()) as theMonth  -- returns 11
SELECT DATEPART(d, GETDATE()) as theDay   -- returns 20
SELECT DATEPART(yy, GETDATE()) as theYear   -- returns 2013

Now that I have Month, Day, Year values individually, I pass it to my DATENAME to get the Weekname of the date I want:

--my SQL query to return dayName
SELECT (DATENAME(dw, DATEPART(m, GETDATE())/DATEPART(d, myDateCol1)/ DATEPART(yy, getdate())))  as myNameOfDay, FirstName, LastName FROM myTable

This returns an incorrect Day Name. I tried replace / with - so that in the DATENAME function my SQL query becomes:

SELECT DATENAME(dw,'09/23/2013') 
--becomes
SELECT DATENAME(dw,'09-23-2013') 

but it still returns incorrect dayName from my SQL query. Am I missing something here.

Please advise.

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))
  )

If you have SQL Server 2012:

If your date parts are integers then you can use DATEFROMPARTS function.

SELECT DATENAME( dw, DATEFROMPARTS( @Year, @Month, @Day ) )

If your date parts are strings, then you can use the CONCAT function.

SELECT DATENAME( dw, CONVERT( date, CONCAT( @Day, '/' , @Month, '/', @Year ), 103 ) )

Try like this: select DATENAME(DW,GETDATE())