How can I change the format of the dates produced when using ALTER COLUMN to convert a datetime column to varchar?

I have a datetime column that I need to alter to be a varchar column.

Using the statement below, the strings produced have this format: "Jan 18 2010 5:28PM"

ALTER TABLE Thinger
ALTER COLUMN LastUpdateDate varchar(16) NOT NULL

I would like strings produced to开发者_JS百科 have a yyyyMMdd format (giving 20100118) instead. Is there a way to do this?

Thanks

---

Bad, Bad idea...never ever store dates in varchar columns, now you will get garbage in there in different formats Also why varchar(16) when you want yyyyMMdd?

if you want the output to be in a different format do it in the presentation layer or use convert

SELECT CONVERT(CHAR(8),GETDATE(),112)

now if you really want to do what you say you want to do

run your script and then do

UPDATE Table 
SET LastUpdateDate  =  CONVERT(CHAR(8),(CONVERT(DATETIME,CONVERT(varchar,LastUpdateDate))),112)

But again..bad bad bad idea

Also the next version of SQL Server will make formatting a lot easier see: Format function in SQL Server Denali CTP3

---

Try this script:

ALTER TABLE Thinger ADD LastUpdateDateText VARCHAR(16) NOT NULL
GO

UPDATE Thinger SET LastUpdateDateText = CONVERT(VARCHAR(8), LastUpdateDate, 112)
GO

ALTER TABLE Thinger DROP COLUMN LastUpdateDate
GO

sp_RENAME 'Thinger.LastUpdateDateText' , 'LastUpdateDate', 'COLUMN'
GO