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.

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[rbr94]

Please provide some more details (e.g. a simple explanation of what you SQL script does) to your answer.

[Praveen Patel G]

select DATENAME(DW,GETDATE()); This is enough to get day name.

[Eli Gassert]

That wasn't the issue or question, @AuthorPraWin

[A. Murray]

@EliGassert granted, but this is the top Stack Overflow result in Google for the term 'tsql day name' and this answer along with AuthorPraWin's useful addendum helped me immediately.

[Ergi Nushi]

The database that OP is using is SQL Server. Your response is for Oracle. It will not work.

[Mak]

In the question, it's already mentioned that DB is SQL. You wrote it for Oracle