how to convert text to date in sql server 2008?

Question

i have table that contain A text field [nvarchar(10)]

how to convert this field to date ?

when i try to change in the field in the design mode - i get this:

Unable to modify table.

Conversion failed when converting date and/or time from character string.

is there any query that i can run to do it ?

thank's in advance

Answer 1

You could to follow this steps:

• Create a new datetime column, nullable
• Update that column with properly casted values
• Drop your original column
• Rename and adjust your column to do not accept nulls, if applicable

Try this sample:

CREATE TABLE #Sample
(
    FieldAsText varchar(10) NOT NULL
);
GO

INSERT INTO #Sample VALUES ('2009-01-24');

ALTER TABLE #Sample ADD FieldAsDate datetime NULL
GO

UPDATE #Sample SET FieldAsDate = CONVERT(DATETIME, FieldAsText)

SELECT * FROM #Sample 

ALTER TABLE #Sample DROP COLUMN FieldAsText
ALTER TABLE #Sample ALTER COLUMN FieldAsDate datetime NOT NULL
GO

SELECT * FROM #Sample