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

User · Answer

I used  select case when  extract  weekday from DATE  0  then  Sunday    and so on     0 Sunday  1 Monday

User · Answer

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

User · Answer

SELECT DATENAME DW CONVERT VARCHAR 20  GETDATE   101

User · Answer

Try like this  select DATENAME DW GETDATE

User · Answer

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

User · Answer

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

User · Answer

select to char sysdate  DAY   from dual  It s  work try it

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

Examples related to sql

Examples related to sql-server

Examples related to function

Examples related to date