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

Question

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.

Answer 1

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.