CODEWITHSUNDEEP
sqlsql-server
Arpita
Arpita

Reputation: 455

How to insert weekdays names in sql column based on given date?

I wanted to know how to insert weekday names in a column in sql server based on the current date. I want to insert the days until current day. For example, I want something as per the following column for today (2014-12-04),

day_of_week
-----------
Monday 
Tuesday
Wednesday
Thursday

On next Tuesday(2014-12-09), I want to display,

day_of_week
-----------
Monday
Tuesday

Let me know how can I do this in sql server 2012?

Any help will be appreciated.

Thanks.

Upvotes: 0

Views: 1770

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

It can also be done using a recursive CTE:

;WITH cte AS
(   
   SELECT DATENAME(weekday,getdate()) AS NameOfDay, 
          DAY(getdate()) AS NumberOfDay,
          getdate() AS curDate                   

   UNION ALL

   SELECT DATENAME(weekday,DATEADD(day, -1, curDate)) As NameOfDay, 
          DAY(DATEADD(day, -1, curDate)) AS NumberOfDay,
          DATEADD(day, -1, curDate) AS curDate
   FROM cte
   WHERE DAY(GETDATE()) - DAY(DATEADD(day, -1, curDate)) >= 0
)
SELECT NameOfDay
FROM cte  
ORDER BY NumberOfDay

Upvotes: 1

bobthedeveloper
bobthedeveloper

Reputation: 3783

Something like this should work.

select datename(weekday,n) 
from (VALUES (0),(1),(2),(3),(4),(5),(6)) wdays(n)
where n < datepart(weekday,'20141209') - 1

And if you're not using sql 2008 and up you can do

select datename(weekday,n) 
from (select 0
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
) wdays(n)
where n < datepart(weekday,'20141209') - 1

Upvotes: 2

Related Questions