Changing DataType of a Column in SQL

Question

I have a DateTime(null) column as StartDate in one of my tables schema. Clients already have entered lots of data in their table based on that. Now they want that column to be of type Character(10) Is it possible to do that? Won't it damage the data they already have entered? What is a safe way to do this?

Why do the clients want to change the datatype? If they want to enter data that represents the start date then the DateTime should be suitable; if they want to enter data that does not represent the start date then a new, appropriately-named column should be set up. — user359040
– user359040, Commented Nov 16, 2015 at 13:44
Just say no. Since when is the client responsible for database design? if the values are datetime, or date, storing them in char can only do harm. you might want to change the database from datetime to date, but do not agree to change it to char under any circumstances. — Zohar Peled
– Zohar Peled, Commented Nov 16, 2015 at 13:44
@Bohn . . . Then default the day to 01 and only show it to them using the year and month. — Gordon Linoff
– Gordon Linoff, Commented Nov 16, 2015 at 13:46

Matt · Accepted Answer · 2015-11-16 14:00:25Z

2

SELECT StartDate, CONVERT(CHAR, StartDate, 10) AS NEWOUTPUT
FROM yourtable

If the results of the NEWOUTPUT column are fine then go ahead with..

alter table yourtable alter column StartDate char(10) not null;

edited Nov 16, 2015 at 14:00

answered Nov 16, 2015 at 13:46

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bohn Over a year ago

Now how do I do the actual conversion? Thanks.

Matt Over a year ago

@SQLPolice , thanks! i appreciate these however anymore and they will get revesed by the up vote script i would imagine as its too many upvotes from the same user to the same user per day

Thomas · Accepted Answer · 2015-11-16 13:47:37Z

A direct change could lead to troubles. You would need to

Create a new column
Update your table so that the new column is filled with YEAR(datecolname) + '-' + MONTH(datecolname) + '-' + DAY(datecolname).
Delete the old column
Rename the new column to the old name
Recreate indexes

One thing though is that the searches for specific dates WILL probably be slower in any case if you use char instead of datetime and specific sql statements like "between" won't work any longer.

If it is just as you mentioned in a comment because they dont want to type in a day, then it would probably be better to change how they have to type in the dates so that the day is defaultwise set to 1. But depends on the application in question.

Collectives™ on Stack Overflow

Changing DataType of a Column in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related