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

datefunctionsqlsql 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.

Best 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.

Related Solutions

SQL Server – Best way to get identity of inserted row

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

Python – How to get the last day of the month

calendar.monthrange provides this information:

calendar.monthrange(year, month)
Returns weekday of first day of the month and number of days in month, for the specified year and month.

>>> import calendar
>>> calendar.monthrange(2002, 1)
(1, 31)
>>> calendar.monthrange(2008, 2)  # leap years are handled correctly
(4, 29)
>>> calendar.monthrange(2100, 2)  # years divisible by 100 but not 400 aren't leap years
(0, 28)

so:

calendar.monthrange(year, month)[1]

seems like the simplest way to go.

Related Topic