[sql] 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.

select to_char(sysdate,'DAY') from dual; It's work try it

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.

I used

select
case
when (extract (weekday from DATE)=0) then 'Sunday'

and so on...

0 Sunday, 1 Monday...

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

SELECT DATENAME(DW,CONVERT(VARCHAR(20),GETDATE(),101))