Alter column data type from varchar to datatime

Question

Alter column data type from varchar to datatime

kokyan

SSCommitted

Points: 1694
More actions
January 27, 2004 at 8:56 pm

#106853

Dear Experts,
I need to alter the data type of a existing column in a table (large table with a lot of data). The original data type is DATETIME.
Now, I want to change the data type to VARCHAR(50). Unfortunately, the result is not want I want, e.g. "Aug 8 2003 12:00AM". I want the result in the same format like datetime, e.g. "08/08/2003".
Is there any methods to convert the datatype to the desired format after the changes to the datatype?

Regards,
kokyan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ckempste SSCoach Points: 17983 More actions · Answer 1

Hi - good question

I dont know of a method off hand with standard date format commands during an alter, I would probably go down the path of adding a temporary column of varchar(50), port the data over, then drop the old col and rename the new. Nice and safe; OR use another temp table with the PK of the table + this datetime column and take it from there with an update back.

Cheers

Ck

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

kokyan SSCommitted Points: 1694 More actions · Answer 2

Dear ckempste,

I already thought of your idea before but it takes a lot of time. I need to change quite a lot of tables. Any other ideas?

Regards,
kokyan

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

use tempdb

go

CREATE TABLE Test(

Id int IDENTITY PRIMARY KEY,

Dt datetime)

INSERT Test(Dt) VALUES('20030808')

ALTER TABLE Test ALTER COLUMN Dt varchar(50)

UPDATE Test SET Dt = CONVERT(char(10),CAST(Dt AS datetime),101)

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

May I ask, why?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

guarddata SSCertifiable Points: 6583 More actions · Answer 5

I'm with Frank. There must be some need where this column is now going to store more than just date information. If the concern is formatting, that should probably be an issue of the display process. If the concern is searching and dealing with time portions, there are better ways to resolve the problem. Handling dates as strings causes all kinds of side effects - not the least of which is the sorting/searching problems that arise.

Do you have time to explain the reason? Perhaps there is a less painful approach.

Guarddata-

control SSCommitted Points: 1630 More actions · Answer 6

control

SSCommitted

Points: 1630

January 30, 2004 at 1:45 pm

#492405

How can I post a question to the expert

guarddata SSCertifiable Points: 6583 More actions · Answer 7

Hehe - Pretty hard to find people that know more than the responses I have seen here. Give it a try right here.

Guarddata-

kokyan SSCommitted Points: 1694 More actions · Answer 8

I agree with 'guarddata'! I am proud to said I am member of SQL ServerCentral forum. This forum consists of thousands of expert!

Thanks to the suggestions that posted by most of the experts and I think it over, there are no points to convert the datetime to string, only because to store the date with the desired format. I already solve the problem with CONVERT() whenever need to display.

Regards,
kokyan