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

date, function, sql, sql-server

Solution

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

Problem

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.

Original source