Posted By: Anonymous
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:
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.
You need to construct a date string. You’re using
- 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)) )