how to alter a column to load the datepart from another datetime column

Question

how to alter a column to load the datepart from another datetime column

Polachan Paily 226

I have a table

Create table Attendance(id int, ClockedDate datetime, LogDate datetime)
values
(1, 2019-02-25 08:52:15.000, null),
(2, 2019-02-25 08:57:51.000, null)

I want to alter the column LogDate to store date part only from ClockedDate column in the table Attendance

Please help how to alter the existing column to get the date from another column virtually.

Accepted answer

1 additional answer

Your answer

Answer 1

Viorel 122.6K

Try the next script:

alter table Attendance drop column LogDate
alter table Attendance add LogDate as cast(ClockedDate as date)

Check if it worked using select * from Attendance.

Answer 2

Hi @Polachan Paily ,

Do you consider using the update statement?

alter table Attendance alter column LogDate date  null;  
update Attendance  
set LogDate=cast(ClockedDate as date)  
from Attendance  
  
select * from Attendance

Output：

    id ClockedDate LogDate   
    1 2019-02-25 08:52:15.000 2019-02-25  
    2 2019-02-25 08:57:51.000 2019-02-25

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

how to alter a column to load the datepart from another datetime column

1 additional answer

Your answer