CODEWITHSUNDEEP
sql-servert-sql
TheNewone
TheNewone

Reputation: 97

Alter table to get day name only from existing data

I have a table with a datetime column from which I need to extract the name of the day only and add to a new column like dayNameOnly and as default there after.

I have done the same to get the DATEONLY like this, but I cannot get it working with the datename or datepart.

ALTER TABLE someTable 
    ADD dayNameOnly AS CAST(DATEADD(DAY, DATADIFF(DAY, 0, someDateColounm), 0) as DATE)

Upvotes: 0

Views: 149

Answers (3)

Dr.Stark
Dr.Stark

Reputation: 116

create table #mytbl
(mydate  as DATENAME(WEEKDAY,getdate()),sno int)


insert into #mytbl(sno)
values (1)

select * from #mytbl

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

If you are trying to add computed column somedatecolumn. Here's the script.

alter table someTable 
add dayNameOnly 
As cast(dateadd(day, datediff(day, 0, someDateColounm), 0) as date)

or, if you want to extract the weekday name as a computed column.

alter table someTable 
add dayNameOnly
as datename(dw, someDateColounm)

Upvotes: 1

Squirrel
Squirrel

Reputation: 24773

You wanted the weekday name in string ?

ALTER TABLE someTable 
ADD dayNameOnly 
As datename(weekday, someDateColounm)

On the query that you have shown, you do not need to perform the dateadd/datediff calculation CAST(DATEADD(DAY, DATADIFF(DAY, 0, someDateColounm), 0) as DATE). You can simply cast() or convert() it to date 

CAST(someDateColounm as DATE)

Upvotes: 1

Related Questions