Alter datetime column to varchar with existing data

Question

I have table A and it has a column named LastUpdateDate in datetime format. It keeps date with time data like Apr 18 2017 11:15AM. I want to get rid of time data and keep date in dd/mm/yyyy format.

So I guess, I alter the the column to VARCHAR. But I have existing data. Do I get error when I alter the column? Or are there any other option? What should I do?

Answer 1

You should not alter the column to a varchar.
If anything, you should alter it to a date data type:

 ALTER TABLE YourTable
 ALTER COLUMN LastUpdateDate DATE
 GO

Here is a sample table with some data (Please save us this step in your future questions):

CREATE TABLE YourTable 
(
    id int identity(1,1),
    LastUpdateDate datetime
)

INSERT INTO YourTable VALUES 
(GETDATE()),
(DATEADD(DAY, 1, GETDATE())),
(DATEADD(DAY, 2, GETDATE())),
(DATEADD(DAY, 3, GETDATE())),
(DATEADD(DAY, 4, GETDATE()))

The alter table statement:

 ALTER TABLE YourTable
 ALTER COLUMN LastUpdateDate DATE
 GO

Test:

 SELECT *
 FROM YourTable

Result:

id  LastUpdateDate
1   18.04.2017 00:00:00
2   19.04.2017 00:00:00
3   20.04.2017 00:00:00
4   21.04.2017 00:00:00
5   22.04.2017 00:00:00

See a live demo on rextester.