CODEWITHSUNDEEP
sql-serversql-server-2008date
Tomas H
Tomas H

Reputation: 19

SQL query to return 'name of day' using 'day of year'

I'm trying to write a MSSQL query that will return the name of the day (e.g. Monday, Tuesday...) using the supplied day of year (e.g. 1 for 1st of January, 2 for 2nd of January).

SELECT DATENAME(dy, '2016-03-01') - returns 61

SELECT DATENAME(dw, '2016-03-01') - returns Tuesday

SELECT DATENAME(dw, 61) - returns Saturday

I want my 3rd query to return the correct day name (Tuesday in this instance), using the supplied day of year (61 in this instance). I understand that the problem is to do with the date portion in DATENAME(datepart, date), as it is in the incorrect date format but I do not have the full date, only the day of year.

Many thanks!

Upvotes: 0

Views: 53

Answers (1)

Squirrel
Squirrel

Reputation: 24763

DECLARE @day_of_year int

SELECT @day_of_year = 61
SELECT DATENAME(dw, DATEADD(DAY, @day_of_year - 1, '2016-01-01'))

Upvotes: 1

Related Questions