Alter column data type from varchar to datatime

  • 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?




  • 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.




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

  • 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?


  • use tempdb




    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)


  • May I ask, why?

    Frank Kalis
    Microsoft SQL Server MVP
    My blog:[/url]

  • 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.


  • How can I post a question to the expert

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


  • 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.


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

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